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

hallo,

 

I have the following table:

 

table1.PNG

and I want to obtain the following one:

table2.PNG

 

where :

Delta 19/18 is the difference of the Prod values between the 2 years by structure and month.

Evolution in Percent 19/18  is the %age change of the Prod values between the 2 years by structure and month.

 

How can I programm this?

 

thanks for your help.

PY

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

This is one way IF there are only 2 years involved. If you want to do sequential multiple years you need to say so.

data Beispiel(label='Beispiel');
input Year:8. Month:2. Structure:$35. Prod:8.; /* Prod is a numeric value, actually */
datalines;
2018 1 AAA 100
2019 1 AAA 110
2019 2 BBB 120
2018 2 AAA 250
2018 3 AAA 260
2019 2 BBB 270
2019 3 AAA 370
2018 3 AAA 380
2019 5 BBB 490
2018 4 AAA 410
2019 6 AAA 511
2019 5 BBB 612
;
run;


proc summary data=Beispiel nway;
   class structure month year ;
   var prod;
   output out=work.sums (drop= _:) sum=;
run;

proc transpose data=work.sums
    out=work.trans (drop=_name_) prefix=y;
   by structure month;
   id year;
   var prod;
run;

data work.want;
   set work.trans;
   if missing( y2018) and not missing(y2019) then do;
      delta=y2019;
      pct=100;
   end;
   else    if missing( y2019) and not missing(y2018) then do;
      delta= -1*y2018;
      pct  = -100;
   end;
   else    if missing( y2019) and missing(y2018) then do;
      delta= 0;
      pct  = 0;
   end;

   else do;
      delta = y2019-y2018;
      pct = 100 * delta/y2018;
   end;
   
run;

I have left adding the months without any actual values as an exercise for the interested reader.

This is NOT the only way, just one way. I left the individual year totals for each month so you can check the data to see if it meets your expectations.

 

Personally, I find the concept of assigning a delta or percent change when there is only one value suspect in many ways.

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

We need to have your sample data provided as a SAS data step, following this method: 

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

--
Paige Miller
PierreYvesILY
Pyrite | Level 9

hi Paige,

 

here's the original dataset:

 

data Beispiel(label='Beispiel');

input Year:8. Month:2. Structure:$35. Prod:8.; /* Prod is a numeric value, actually */

datalines;

 

2018 1 AAA 100

2019 1 AAA 110

2019 2 BBB 120

2018 2 AAA 250

2018 3 AAA 260

2019 2 BBB 270

2019 3 AAA 370

2018 3 AAA 380

2019 5 BBB 490

2018 4 AAA 410

2019 6 AAA 511

2019 5 BBB 612

;

run;

 

ballardw
Super User

I can spend lots of time writing specific calculations but that is not the way computers really should work. There needs to be generic rules for how to combine things.

 

For instance, what in the data tells me that the order should be value7-value8-value5 and not value5-value8-value7 or value5-value7-value8 or any of the other permutations of those 3 values.  I can see that all of the values come from the same value of month and structure but is the YEAR involved? If so describe exactly how. And even that does not tell use that the order should be value8-value5 instead of value5-value8 for the 2018 values.

 

And why is BBB for month2 Value3+Value6 when for AAA everything appeared to be minus.

 

You are missing LOTS of rules as to how to calculate what you might want.

PierreYvesILY
Pyrite | Level 9

the formula is:

- select month then structure

- then do:

 Delta =sum(values of 2019) - sum(values of 2018)

- then do:

EvolPercent = 100*Delta / sum(values of 2018)

 

I hope it sets the problem in a more easy way to understand.

 

regards,

PY

Reeza
Super User
How does month play into this? Is it a YTD Percent increase calculation or a Year over Year percent calculation. It would be very helpful if you could post the expected output from the data you included as demo data.
PierreYvesILY
Pyrite | Level 9

The calculation is a month(2019) to month(2018) comparison.

 

here is the result dataset:

 

data Result;

input Month:2. Structure:$35. Delta:8. EvolPercent:8.;

datalines;

 

1 AAA 10 10

2 AAA -250 -100

3 AAA -270 -42.1875

4 AAA -410 -100

5 AAA 0 0

6 AAA 511 100

1 BBB 0 0

2 BBB 390 100

3 BBB 0 0

4 BBB 0 0

5 BBB 1022 100

6 BBB 0 0

;

run;

 

 

thanks a lot

PY

ballardw
Super User

This is one way IF there are only 2 years involved. If you want to do sequential multiple years you need to say so.

data Beispiel(label='Beispiel');
input Year:8. Month:2. Structure:$35. Prod:8.; /* Prod is a numeric value, actually */
datalines;
2018 1 AAA 100
2019 1 AAA 110
2019 2 BBB 120
2018 2 AAA 250
2018 3 AAA 260
2019 2 BBB 270
2019 3 AAA 370
2018 3 AAA 380
2019 5 BBB 490
2018 4 AAA 410
2019 6 AAA 511
2019 5 BBB 612
;
run;


proc summary data=Beispiel nway;
   class structure month year ;
   var prod;
   output out=work.sums (drop= _:) sum=;
run;

proc transpose data=work.sums
    out=work.trans (drop=_name_) prefix=y;
   by structure month;
   id year;
   var prod;
run;

data work.want;
   set work.trans;
   if missing( y2018) and not missing(y2019) then do;
      delta=y2019;
      pct=100;
   end;
   else    if missing( y2019) and not missing(y2018) then do;
      delta= -1*y2018;
      pct  = -100;
   end;
   else    if missing( y2019) and missing(y2018) then do;
      delta= 0;
      pct  = 0;
   end;

   else do;
      delta = y2019-y2018;
      pct = 100 * delta/y2018;
   end;
   
run;

I have left adding the months without any actual values as an exercise for the interested reader.

This is NOT the only way, just one way. I left the individual year totals for each month so you can check the data to see if it meets your expectations.

 

Personally, I find the concept of assigning a delta or percent change when there is only one value suspect in many ways.

PierreYvesILY
Pyrite | Level 9

hello,

thank you for your answer.

 

I had to take some time to read the documentation and adapt the suggested code to my real data, but it worked perfectly.

Thanks a lot for your help,

Regards,

PY

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 2109 views
  • 4 likes
  • 4 in conversation