Multiplying Opservations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Multiplying Opservations

Hi everybody,

my table looks like:

ProductScenariosMeasure201101
xxxBudgetSales Volume110
xxxBudgetSales Value???A
xxxBudgetSales Value Variance0
xxxBudgetPrice5
xxxActualSales Volume200
xxxActualSales Value???B
xxxActualSales Value Variance???C
xxxActualPrice10

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)

ProductScenariosMeasure201101
xxxBudgetSales Volume110
xxxBudgetSales Value550A
xxxBudgetSales Value Variance0
xxxBudgetPrice5
xxxActualSales Volume200
xxxActualSales Value2000B
xxxActualSales Value Variance1450C
xxxActualPrice10

thank you for your help.

Kind regards.

Toufik


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

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;

View solution in original post


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: 5,091

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: 5,091

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 292 views
  • 6 likes
  • 3 in conversation