Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 08-02-2019 11:29 AM
(2108 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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**;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

**Available on demand!**

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

Upcoming Events

- SAS® Viya® Workbench – Available on AWS Marketplace | 25-Jun-2024
- DCSUG Virtual Meeting with Kirk Paul Lafler | 26-Jun-2024
- Wie kann ich SAS Visual Analytics effizient einsetzen, um die Ergebnisse meiner analytischen Modelle | 27-Jun-2024
- Ask the Expert: How Can I Use SAS® Optimization From Python? | 09-Jul-2024
- WUSS Virtual: Mastering Oncology Studies: A Comprehensive Guide for Programmers & Biostatisticians | 12-Jul-2024
- Ask the Expert: Jupyter Notebook: Your Coding Canvas | 16-Jul-2024
- MinnSUG Annual SAS Conference | 17-Jul-2024

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.