How to convert SAS Code to SQL ?

[prabha8934]

data covg_overlap_flag_2019;
set covg_rec_2019_sorted (keep=POIDS);
by POIDS;
merge covg_rec_2019_sorted
covg_rec_2019_sorted (firstobs=2 keep=BNFT_CVG_EFFECTIVE_DT BNFT_CVG_TERM_DT rename=(BNFT_CVG_EFFECTIVE_DT=next_sd)
rename=(BNFT_CVG_TERM_DT=next_ed));
if (first.POIDS=0 and BNFT_CVG_EFFECTIVE_DT<lag(BNFT_CVG_TERM_DT)) or
(BNFT_CVG_TERM_DT>next_sd and last.POIDS=0) then flag+1;
else
if (first.POIDS=0 and BNFT_CVG_EFFECTIVE_DT=lag(BNFT_CVG_EFFECTIVE_DT))
or (first.POIDS=0 and BNFT_CVG_TERM_DT=lag(BNFT_CVG_TERM_DT))
then flag+1;

else if (last.POIDS=0 and BNFT_CVG_EFFECTIVE_DT=next_sd) or
(BNFT_CVG_TERM_DT=next_ed and last.POIDS=0)
then flag+1;
else flag=0;
run;

[ballardw]

Since SQL really doesn't have much of a concept of reading records by order anything that uses First. or Last. constructs in a data step is going to require a lot of not trivial SQL to get an equivalent. And that is a maybe.

Use of LAG and DIF functions is right out. You would need a separate ordered join to get each "lag" value.

If that works why would want to replace it?

[prabha8934]

Thank you. That's true SQL doesn't have many features like SAS

[smantha]

Some SQL languages support lag and (may be dif) but I haven't seen a construct where first. and last. exist. Things that come close to this behavior is using a windowing function and creating a non tied rank. Us teh rank so obtained in the processing

[Reeza]

What flavour of SQL? PL/SQL or cursor logic may help but it's painful.

[prabha8934]

T-SQL

[SASKiwi]

IMHO, don't use SQL for purposes it wasn't designed for. The code you've supplied isn't easily translatable into T-SQL. What is wrong with continuing to process in SAS and then load the results into the database?