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!
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.