## Multiplying Opservations

Solved
Occasional Contributor
Posts: 9

# Multiplying Opservations

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

Kind regards.

Toufik

Accepted Solutions
Solution
‎02-07-2014 10:36 AM
Super User
Posts: 6,781

## Re: Multiplying Opservations

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;

All Replies
Regular Contributor
Posts: 195

## Re: Multiplying Opservations

Just transpose the dataset and apply formula based on PRODUCT and BUDGET.

Tell me if you can't fix it.

-Urvish

Occasional Contributor
Posts: 9

## Re: Multiplying Opservations

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.

Super User
Posts: 6,781

## Re: Multiplying Opservations

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.

Solution
‎02-07-2014 10:36 AM
Super User
Posts: 6,781

## Re: Multiplying Opservations

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;

Occasional Contributor
Posts: 9

## Re: Multiplying Opservations

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

🔒 This topic is solved and locked.