Hi everybody,
my table looks like:
Product | Scenarios | Measure | 201101 | |
xxx | Budget | Sales Volume | 110 | |
xxx | Budget | Sales Value | ??? | A |
xxx | Budget | Sales Value Variance | 0 | |
xxx | Budget | Price | 5 | |
xxx | Actual | Sales Volume | 200 | |
xxx | Actual | Sales Value | ??? | B |
xxx | Actual | Sales Value Variance | ??? | C |
xxx | Actual | Price | 10 |
I want to create a simple code that calculates the items A, B and C for each and every product and Scenario where :
A: For Product= X and Scenario= Budget ==> Sales Value = Sales Volume*Price
B: For Product= X and Scenario= Actual ==> Sales Value = Sales Volume*Price
C: For Product= X and Scenario= Actual ==> Sales Value Variance (For Actual Scenario) = Sales Value (For Actual Scenario) - Sales Value (For Budget Scenario)
Product | Scenarios | Measure | 201101 | |
xxx | Budget | Sales Volume | 110 | |
xxx | Budget | Sales Value | 550 | A |
xxx | Budget | Sales Value Variance | 0 | |
xxx | Budget | Price | 5 | |
xxx | Actual | Sales Volume | 200 | |
xxx | Actual | Sales Value | 2000 | B |
xxx | Actual | Sales Value Variance | 1450 | C |
xxx | Actual | Price | 10 |
thank you for your help.
Kind regards.
Toufik
I should note ... this solution might be overkill. It assumes that you have other variables (not shown in your example) that you need to preserve. If the only variables are the ones that you show, you don't need a second DO UNTIL loop. Instead, it could be replaced with:
* IF/THEN statements to adjust the 8 new variables;
Then reverse the transposition in the same DATA step:
Scenarios = 'Budget';
Measure = 'Sales Volume';
_201101_ = budget_sales_volume;
output;
Measure = 'Sales Value';
_201101_ = budget_sales_value;
output;
Measure = 'Sales Value Variance';
_201101_ = budget_sales_variance;
output;
Measure = 'Price';
_201101_ = budget_sales_price;
output;
Scenarios = 'Actual';
* same idea for the 4 "actual" variables;
Just transpose the dataset and apply formula based on PRODUCT and BUDGET.
Tell me if you can't fix it.
-Urvish
Hi Urvish,
I already tried the Transpose solution which consist to:
-Transpose first the column Measure to calculate A and B;
-Then Transpose the column Scenario to calculate C.
however my aim is to optimize the program by using a simple code (as for Cube queries) because my table contains millions of observations.
thank you.
Regards.
Here's another approach. The first half of the DATA step accomplishes the equivalent of transposing the data:
data want;
do until (last.product);
set have;
by product;
if scenarios='Budget' then do;
if measure='Sales Volume' then budget_sales_volume = _201101_;
else if measure='Sales Value' then budget_sales_value = _201101_;
else if measure='Sales Value Variance' then budget_sales_variance = _201101_;
else if measure='Price' then budget_sales_price = _201101_;
end;
else if scenarios='Actual' then do;
* same idea, create 4 new variables;
end;
end;
drop /* all 8 newly created variables */ ;
Then the second half of the DATA step can make all the necessary calculations.
do until (last.product);
set have;
by product;
* whatever IF/THEN statements you need to adjust the values;
output;
end;
run;
Good luck.
I should note ... this solution might be overkill. It assumes that you have other variables (not shown in your example) that you need to preserve. If the only variables are the ones that you show, you don't need a second DO UNTIL loop. Instead, it could be replaced with:
* IF/THEN statements to adjust the 8 new variables;
Then reverse the transposition in the same DATA step:
Scenarios = 'Budget';
Measure = 'Sales Volume';
_201101_ = budget_sales_volume;
output;
Measure = 'Sales Value';
_201101_ = budget_sales_value;
output;
Measure = 'Sales Value Variance';
_201101_ = budget_sales_variance;
output;
Measure = 'Price';
_201101_ = budget_sales_price;
output;
Scenarios = 'Actual';
* same idea for the 4 "actual" variables;
Hi Astouding,
thanks a lot for your help, you idea is very good but unfortunately my table contain monthly data for 8 years (201101-201801), which makes the code too long,
I have found a simpler method:
create a Key=Scenario||Measure (concatenate), then I dropped the scenario variable and performed a transpose to have the measures in coulmns and applied my formulas.
I transposed back my data.
thank you very much for your help.
Kind regards.
Toufik
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.