Hi.
I have this sample table.
Group | Type | Value |
1 | a | 16 |
1 | b | 25 |
2 | a | 20 |
2 | b | 14 |
3 | a | 17 |
3 | b | 31 |
I want to calculate the percentage change from a to b per group. For example formula for Group 1 = (16-25)/16 * 100.
Thank you.
Yoyong
Like this?
data WANT;
set HAVE;
by GROUP
PCT = divide(-dif(VALUE),lag(VALUE)) * last.GROUP * 100;
run;
Like this?
data WANT;
set HAVE;
by GROUP
PCT = divide(-dif(VALUE),lag(VALUE)) * last.GROUP * 100;
run;
Or:
data WANT;
set HAVE;
PCT = divide(-dif(VALUE),lag(VALUE)) * (TYPE='b') * 100;
run;
Thanks. How will the formula look like if it is from b to a?
Your data is sorted by TYPE, and both code snippets take advantage of this.
Even if the data are sorted by group, but not always sorted by type within group, a use of the WHERE dataset name parameter in a SET statement can address the problem without doing a sort:
data WANT;
set HAVE (where=(type='a')
have (where=(type='b');
by group;
pctchnge1=ifn(type='b',divide(dif(value),lag(value)),.);
run;
The BY statement tells SAS that the data are sorted by group. But the SET statement forces the type a's to alway precede the type b's within a group, regardless of how they are actually ordered.
So if you want the pct change from b to a instead of a to b, you can do one of these:
Note this assumes that each group has exactly one type a and one type b record, but ignores other types.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.