topic Re: Multiplying Opservations in SAS Programming

Multiplying Opservations

SASSAMA — Fri, 07 Feb 2014 09:22:00 GMT

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

Re: Multiplying Opservations

UrvishShah — Fri, 07 Feb 2014 09:33:19 GMT

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

Tell me if you can't fix it.

-Urvish

Re: Multiplying Opservations

SASSAMA — Fri, 07 Feb 2014 10:22:52 GMT

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.

Re: Multiplying Opservations

Astounding — Fri, 07 Feb 2014 15:02:15 GMT

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;

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.

Re: Multiplying Opservations

Astounding — Fri, 07 Feb 2014 15:36:11 GMT

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;

Re: Multiplying Opservations

SASSAMA — Sat, 08 Feb 2014 09:10:20 GMT

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