How to calculate % change between rows in PROC SQL

[alicezwang96]

I have a table like this:

Yearmonth       ID        Sales

202001            123      1000

202002            123      1500

202003            123        800

202004            123      2000

202005            123      1300

202001             456     2000

202002             456     2500

202003             456     2700

I want to create a new table that calculates the % change between every yearmonth, group by the ID of the salesperson. How can I do this?

[Dilmer]

This is a possible solution although it is not in sql but it works

data have;
input Yearmonth ID Sales;
dif= (((lag(Sales) + Sales)*100)/lag(Sales))-100;
format dif 8.2;
Cards;
202001 123 1000
202002 123 1500
202003 123 800
202004 123 2000
202005 123 1300
202001 456 2000
202002 456 2500
202003 456 2700
;
Run;

Greetings Dilmer.

[ballardw]

You may want to show what you would expect from that example data. There is more than one way to interpret " change between every yearmonth" and at least one of those would be nasty in SQL because order of processing is not SQL's strong point.

[Patrick]

Like this?

data have;
  input Yearmonth ID Sales;
  Cards;
202001 123 1000
202002 123 1500
202003 123 800
202004 123 2000
202005 123 1300
202001 456 2000
202002 456 2500
202003 456 2700
;

data want;
  set have;
  by id;
  format dif percent10.2;
  _lag_sales=lag(sales);
  if not first.id then
    do;
      dif=sales/_lag_sales;
    end;
  drop _lag_sales;
run;