topic LAG over partition by in a PROC SQL? in SAS Programming

LAG over partition by in a PROC SQL?

tazz_bh — Tue, 11 May 2021 13:17:18 GMT

How can I use lag over partition by in proc sql?

PROC SQL; 
CREATE TABLE WANT AS 
select t1.account_id
from (select h1.account_id,
             lag(ACCOUNT_STATUS) over (partition by account_id order by ACCOUNT_CHANGE_DATE) as prev_status
      from HAVE h1
     )  t1
where prev_status NOT = 'B1' and ACCOUNT_STATUS = 'B1' and
      account_change_date >= '1Dec2020'd and
      account_change_date < '1Jan2021'd;
	  QUIT;

Thank you!

Re: LAG over partition by in a PROC SQL?

r_behata — Tue, 11 May 2021 14:04:07 GMT

WINDOW functions are not supported in Proc SQL yet .

lag(ACCOUNT_STATUS) over (partition by account_id order by ACCOUNT_CHANGE_DATE) as prev_status

This functionality can be achieved using Data Step.

Re: LAG over partition by in a PROC SQL?

Reeza — Tue, 11 May 2021 14:09:26 GMT

The LAG() function will work in a data step.

Re: LAG over partition by in a PROC SQL?

ballardw — Tue, 11 May 2021 14:43:57 GMT

SAS Proc Sql also does not support "partition by" syntax.

SQL in general is not a sequential processing approach to data. It is based more on set operations if you remember those from junior high math classes. So "lag", which relies on a specific sequence of records, is not implemented for SQL.