topic How to calculate % change between rows in PROC SQL in SAS Programming

How to calculate % change between rows in PROC SQL

alicezwang96 — Thu, 23 Jun 2022 13:38:00 GMT

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?

Re: How to calculate % change between rows in PROC SQL

Dilmer — Thu, 23 Jun 2022 18:37:16 GMT

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.

Re: How to calculate % change between rows in PROC SQL

ballardw — Thu, 23 Jun 2022 21:48:09 GMT

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.

Re: How to calculate % change between rows in PROC SQL

Patrick — Fri, 24 Jun 2022 00:30:05 GMT

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;