topic Re: How to convert SAS Code to SQL ? in SAS Programming

How to convert SAS Code to SQL ?

prabha8934 — Thu, 04 Jun 2020 15:51:29 GMT

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;

Re: How to convert SAS Code to SQL ?

ballardw — Thu, 04 Jun 2020 16:07:30 GMT

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?

Re: How to convert SAS Code to SQL ?

smantha — Thu, 04 Jun 2020 16:51:10 GMT

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

Re: How to convert SAS Code to SQL ?

Reeza — Thu, 04 Jun 2020 17:43:29 GMT

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

Re: How to convert SAS Code to SQL ?

prabha8934 — Thu, 04 Jun 2020 17:43:42 GMT

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

Re: How to convert SAS Code to SQL ?

prabha8934 — Thu, 04 Jun 2020 17:44:25 GMT

T-SQL

Re: How to convert SAS Code to SQL ?

SASKiwi — Thu, 04 Jun 2020 19:56:20 GMT

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?