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

Hello ~ 

Does anyone know the equivalent code using Proc SQL for the following SAS data step? The lag and retain function seems hard to achieve in Proc SQL

data dt;
set dt;
by cin master_id card_issue_dte idx;
retain mn(1);
if cin=lag(cin) and master_id=lag(master_id) and card_issue_dte=lag(card_issue_dte) then mn+1;
if cin=lag(cin) and master_id=lag(master_id) and card_issue_dte ne lag(card_issue_dte) then mn=1;
if cin=lag(cin) and master_id ne lag(master_id) then mn=1;
if cin ne lag(cin) then mn=1;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

Your original data step code can be made a lot simpler like this:

 

data dt;
  set dt;
  by cin master_id card_issue_dte idx;
  if first.card_issue_dte then
    mn=1;
  else 
    mn+1;
run;

To answer your question: no, there is not a similar facility in SAS SQL. The point being that SQL is a declarative language (you tell the compiler/interpreter what you want, not how to get it and in what order), while the SAS data step is procedural (you tell the compiler/interpreter what to do). 

 

Some other SQL dialects have something that may work for what you are doing, e.g. T-SQL (Microsoft):

select *, row_number() over (partition by cin,master_id,card_issue_dt order by idx) as mn from dt;

So if you are actually doing some of your stuff on a database server, look up the local SQL syntax. You may get the result you want with pass through SQL (select * from connection to <server>(<SQL expression>). 

 

But if you are using pure SAS, it is better to stick with the data step.

 

View solution in original post

2 REPLIES 2
s_lassen
Meteorite | Level 14

Your original data step code can be made a lot simpler like this:

 

data dt;
  set dt;
  by cin master_id card_issue_dte idx;
  if first.card_issue_dte then
    mn=1;
  else 
    mn+1;
run;

To answer your question: no, there is not a similar facility in SAS SQL. The point being that SQL is a declarative language (you tell the compiler/interpreter what you want, not how to get it and in what order), while the SAS data step is procedural (you tell the compiler/interpreter what to do). 

 

Some other SQL dialects have something that may work for what you are doing, e.g. T-SQL (Microsoft):

select *, row_number() over (partition by cin,master_id,card_issue_dt order by idx) as mn from dt;

So if you are actually doing some of your stuff on a database server, look up the local SQL syntax. You may get the result you want with pass through SQL (select * from connection to <server>(<SQL expression>). 

 

But if you are using pure SAS, it is better to stick with the data step.

 

Reeza
Super User

@wbsjd wrote:

Hello ~ 

Does anyone know the equivalent code using Proc SQL for the following SAS data step? The lag and retain function seems hard to achieve in Proc SQL

 

Yes it is hard to achieve and significantly more work.

It's why a data step can be more powerful and faster to process data. 

Doing something like this in SQL often means joining the data with itself (self join) along some criteria in this case this seems like logic that could also be accomplished with BY processing.  In general adding row counters is a pain in SQL. Several SQL languages offer alternatives to support this logic, ie CURSOR logic in T-SQL. 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 2 replies
  • 1509 views
  • 3 likes
  • 3 in conversation