BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASSAMA
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
UrvishShah
Fluorite | Level 6

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

Tell me if you can't fix it.

-Urvish

SASSAMA
Calcite | Level 5

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.

Astounding
PROC Star

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.

Astounding
PROC Star

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;

SASSAMA
Calcite | Level 5

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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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