BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tazz_bh
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
r_behata
Barite | Level 11

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.

View solution in original post

3 REPLIES 3
r_behata
Barite | Level 11

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.

Reeza
Super User
The LAG() function will work in a data step.
ballardw
Super User

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3808 views
  • 3 likes
  • 4 in conversation