calculate difference and percentage of difference based on a large dataset

[PierreYvesILY]

hallo,

I have the following table:

and I want to obtain the following one:

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

[PaigeMiller]

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

[PierreYvesILY]

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]

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]

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]

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]

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 · Accepted solution]

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]

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