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.
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.
Lesson assumptions and objectives:
Please refer to the comments (in green) included in the SAS program attached below.
* temportary fact dataset;
format PRICE 8.2;
/* quantity modifier is a value which applies model assumptions regarding */
/* differencies between amount of products sold to retailers vs wholesalers, etc. */
/*seeds are sold in higher quantities than plants*/
if prod_type=1 then
/*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
/* younger plants are bought in bigger quantities */
if prod_age>1 then
*calculate FINAL RANDOM QUANTITY with the use of modifier ;
/*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;
/*test the newly generated data*/
mean min max MAXDEC=2
class cust_group prod_age;
var quantity ;