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;
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.
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.
@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.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: