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;
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?
Thank you. That's true SQL doesn't have many features like SAS
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
T-SQL
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?
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 25. 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.