topic Re: Calculations with lagged values in proc sql in New SAS User

Calculations with lagged values in proc sql

cmemtsa — Mon, 15 Jun 2020 10:55:17 GMT

Hi!
I am trying to make calculations with lagged values within proc sql (I know function lag is not working) as this is only subpart of a longer proc sql program.

Is it feasible?

data T;
input ID $ L M D ;
Datalines;
B 1 10 100 
B 1 20 100
C 1 30 200
C 2 40 200
D 1 50 300
D 2 60 300
D 3 70 300
;

proc sql;
Create table want as 
Select *, 
CASE 
WHEN L=1 THEN D-M
WHEN L=2 then  (SELECT D-LAG(M)  FROM T WHERE ID=A.ID AND L=a.L) 
else (SELECT M-Lag(D)  FROM T WHERE ID=A.ID AND L=a.L) 

END AS D1
FROM T AS A
;
QUIT

Output
B 1 10 100 90
B 1 20 100 80
C 1 30 200 170
C 2 40 200 170
D 1 50 300 250
D 2 60 300 250 
D 3 70 300 -230

Re: Calculations with lagged values in proc sql

PaigeMiller — Mon, 15 Jun 2020 10:57:33 GMT

The LAG function does not work in PROC SQL. Better you should do this in a DATA step.

Re: Calculations with lagged values in proc sql

Jagadishkatam — Mon, 15 Jun 2020 11:07:29 GMT

Please try below code

data T;
input ID $ L M D ;
Datalines;
B 1 10 100 
B 1 20 100
C 1 30 200
C 2 40 200
D 1 50 300
D 2 60 300
D 3 70 300
;

data want;
set t;
by id;
la=lag(m);
if first.id then la=.;
if l=1 then want=d-m;
else if l=2 then want=d-la;
else  want=m-d;

run;

Re: Calculations with lagged values in proc sql

cmemtsa — Mon, 15 Jun 2020 11:53:57 GMT

Thanks. It works with Data but I wanted to know whether is feasible with Proc Sql.