Percent change

[yoyong555]

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

[ChrisNZ · Accepted solution]

Like this?

data WANT;

  set HAVE;

  by GROUP

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

run;

[ChrisNZ]

Or:

data WANT;

  set HAVE;

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

run;

[yoyong555]

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

[ChrisNZ]

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

[mkeintz]

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:

1. swap the order of the where filters in the SET statement, and change the first argument of the IFN function to type='a'.
     or
2. 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.