Proc SQL equivalent for Retain and Lag functinon

[wbsjd]

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;

[s_lassen · Accepted solution]

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]

---

@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.