Hi, I am trying to convert below SAS code into SQL. Please let me know how to do that.

[anksbhardwaj]

Hi, I am trying to convert below SAS code into SQL. Please let me know how to do that. I tried to convert it using lead, lag function but i am not getting desirable output. Please help me in converting this code. I am new to SAS.


data claim_counter;
set merged_clms;
by LINK_ID FIRST_NAME SPCLT_NM start_DT;
if first.FIRST_NAME then claim_counter = 0;
claim_counter+1;
run;

data fill_dt_ref;
set claim_counter;
by LINK_ID FIRST_NAME SPCLT_NM start_DT;
set claim_counter (firstobs= 2 keep=start_DT rename=(start_DT = next_start_dt))
    claim_counter (obs=1 drop = _all_);
last_days= ifn(first.SPCLT_NM,(.),lag(DAYS));
last_dos= ifn(first.SPCLT_NM,(.),lag(adj_value/median_adj_value));
last_ex_dt= ifn(first.SPCLT_NM,(.),lag(ex_dt));
next_start_dt=ifn(last.SPCLT_NM,(.),next_start_dt);
run;

[ChrisNZ]

SQL is not suited to processing rows in an expected order.

Why change to SQL?

[NicoM]

I agree with @ChrisNZ here. This type of processing where the order is important is actually a case where a SAS data step is much easier. Why do you need to have it in SQL?

[Reeza]

Do you understand what the code is doing currently?

What style of SQL are you implementing? it's different if you're using PROC SQL versus Oracle SQL. 

In most SQL languages this is either a really complicated join or it requires cursor logic. Which approach are you leaning towards?

If you can share what you have so far that would help...happy to help but less inclined to do the work entirely. 

[Kurt_Bremser]

Short answer: don't. There are so many data-step specific "tricks" in there that are very hard to re-engineer in SQL.

Maxim 14 says you shall use the right tool, and that is the data step here.