topic Re: Percent change in SAS Programming

Percent change

yoyong555 — Tue, 10 Nov 2020 01:49:02 GMT

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

Re: Percent change

ChrisNZ — Tue, 10 Nov 2020 02:04:07 GMT

Like this?

data WANT;

set HAVE;

by GROUP

PCT = divide(-dif(VALUE),lag(VALUE)) * last.GROUP * 100;

run;

Re: Percent change

ChrisNZ — Tue, 10 Nov 2020 02:05:06 GMT

Or:

data WANT;

set HAVE;

PCT = divide(-dif(VALUE),lag(VALUE)) * (TYPE='b') * 100;

run;

Re: Percent change

yoyong555 — Tue, 10 Nov 2020 02:27:09 GMT

Thanks. How will the formula look like if it is from b to a?

Re: Percent change

ChrisNZ — Tue, 10 Nov 2020 03:06:11 GMT

Your data is sorted by TYPE, and both code snippets take advantage of this.

Re: Percent change

mkeintz — Tue, 10 Nov 2020 06:35:05 GMT

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:

swap the order of the where filters in the SET statement, and change the first argument of the IFN function to type='a'.
or
Leave all of them in place and, using algebra, add a statement:
pctchnge2=1/(1+pctchnge1)-1;

Note this assumes that each group has exactly one type a and one type b record, but ignores other types.