Create Measures and Categories in a Fact table

Generate measures in a Fact table under SAS

Please find below an example of how to generate measures in a fact table and perform some calculations on that figures. The measures will be generated in a random fashion, however they will apply business rules described in business scenario in the introduction for the tutorial.
Lesson assumptions and objectives:

We already have a table filled in with the dimensional data (facts_date_ids)

We will use a Quantity Modifier variable which will help us stick to the business requirements and the business scenario. The modifier will determine the range of quantities sold based on products and customers data. For example the value of the modifier will be higher for wholesales than for retail sales and lower for grown-up plants than for seedlings.

Price is calculated based on the product surrogate key and age. So the higher is the product key and the older is product, then it is worth more.

Revenue is calculated without any rebates or discounts at that stage.

Please refer to the comments (in green) included in the SAS program attached below.

*temportary fact dataset;

data facts_measures;

set facts_date_ids;

*format values;

format PRICE 8.2;

format QUANTITY 8.0;

format REVENUE 8.2;

/* quantity modifier is a value which applies model assumptions regarding */

/* differencies between amount of products sold to retailers vs wholesalers, etc. */

q_modifier=1;

/*seeds are sold in higher quantities than plants*/

if prod_type=1 then q_modifier=q_modifier*3;

/*wholesalers buy more than retailers*/

if cust_segment='Wholesale' then q_modifier=q_modifier*10;

/* Nurseries buy more than other wholesalers */

if cust_group='Nurseries' then q_modifier=q_modifier*20;

/* younger plants are bought in bigger quantities */

if prod_age>1 then do;

q_modifier=10*q_modifier*(1/(prod_age**2));

end;

*calculate FINAL RANDOM QUANTITY with the use of modifier ;

QUANTITY=int(1+q_modifier*abs(rannor(456)));

/*seeds are packed by 10*/

if prod_type=1 then QUANTITY=QUANTITY*10;

/*calculate FINAL PRICE*/

/*price is low for seeds (idp=1) and is getting higher with age */

PRICE = (idp**0.3)*((prod_age)**4)/5;

/*calculate REVENUE*/

REVENUE=price*quantity;

run;

/*test the newly generated data*/

PROC MEANS data=facts_measures mean min max MAXDEC=2 noobs;

class cust_group prod_age;

var quantity ;

RUN;

<-- 2. Populate dimensions

Tutorial Index

4. Sales fact table -->