topic Re: Convert sas code to sql in SAS Programming

Convert sas code to sql

chithra — Fri, 24 Jan 2020 07:57:29 GMT

Hi,

I have a sas code like below :

proc sort data=MIS_TABLES_SEG_F out=MIS_TABLES_SEG_F1;
by AB_IP_SICSID Valdate;
run;

Data MIS_TABLES_SEG_F2;
set MIS_TABLES_SEG_F1;
by AB_IP_SICSID Valdate;
if first.valdate then do;
Gross_EPI_initial_Fx1=Gross_EPI_initial_Fx1;
Gross_Ult_Prem_Fx1=Gross_Ult_Prem_Fx1;
Gross_Unearn_IBNR_Fx1=Gross_Unearn_IBNR_Fx1;

else do;
Gross_EPI_initial_Fx1=.;
Gross_Ult_Prem_Fx1=.;

RUN;

i need to convert this to sql query.

COuld you please help?

Thanks,

CHithra

Re: Convert sas code to sql

yabwon — Fri, 24 Jan 2020 08:12:07 GMT

Hi @chithra ,

Did you try something like this (I don't see your data so consider below as a pseudo-code):

proc sql;
create table MIS_TABLES_SEG_F2 as
  select * , /* list comumns you need */
        case when (valdate = min(valdate)) then Gross_EPI_initial_Fx1
             else . end as Gross_EPI_initial_Fx1,
        .... /* and same for other */
  from MIS_TABLES_SEG_F1
  group by AB_IP_SICSID
  order by AB_IP_SICSID, Valdate
;
quit;

All the best

Bart

Re: Convert sas code to sql

Kurt_Bremser — Fri, 24 Jan 2020 10:38:20 GMT

Your data step code can be simplified to

data MIS_TABLES_SEG_F2;
set MIS_TABLES_SEG_F1;
by AB_IP_SICSID Valdate;
if not first.valdate then do;
  Gross_EPI_initial_Fx1 = .;
  Gross_Ult_Prem_Fx1 = .;
end;
run;

You seem to rely on an implicit order within a valdate group here, if that is true, what is it?

@chithra wrote:

Hi,

I have a sas code like below :

proc sort data=MIS_TABLES_SEG_F out=MIS_TABLES_SEG_F1;
by AB_IP_SICSID Valdate;
run;

Data MIS_TABLES_SEG_F2;
set MIS_TABLES_SEG_F1;
by AB_IP_SICSID Valdate;
if first.valdate then do;
Gross_EPI_initial_Fx1=Gross_EPI_initial_Fx1;
Gross_Ult_Prem_Fx1=Gross_Ult_Prem_Fx1;
Gross_Unearn_IBNR_Fx1=Gross_Unearn_IBNR_Fx1;

else do;
Gross_EPI_initial_Fx1=.;
Gross_Ult_Prem_Fx1=.;

RUN;

i need to convert this to sql query.

COuld you please help?

Thanks,

CHithra

Re: Convert sas code to sql

skhan2 — Wed, 10 Aug 2022 19:23:16 GMT

Hi Team,

Can you pls. help me in converting this sas code to sql. I 've been struggling to find the appropriate content by my own, however no success...

proc sql;
create table active_acct2 as
select &run_month. as period,
atv.applid,
atv.acctnbr,
case when atv.applid='IN' then substr(atv.acctnbr,1,findw(atv.acctnbr,scan(atv.acctnbr,-1))-2)
else atv.acctnbr end as acctnbr_alt,
act.opendt,
act.deptid,
act.prod,
act.cif,
cli.bsa_risk_rating,
cli.customer_category,
cli.shortname
from active_acct atv
left join abc_account5 act
on atv.applid=act.applid and
atv.acctnbr=act.acctnbr
left join dim_client cli
on act.cif=cli.cif
order by applid, acctnbr;
quit;

Re: Convert sas code to sql

SASKiwi — Wed, 10 Aug 2022 20:06:47 GMT

What you've posted is SAS-specific SQL already. You will have to provide more details of what your exact problem is. If you are getting errors in your SAS log you need to post them.

Re: Convert sas code to sql

skhan2 — Wed, 10 Aug 2022 20:10:02 GMT

Hi SASKiwi,
Thank you for looking into. I'm mainly worried on how to translate this line specifically.
case when atv.applid='IN' then substr(atv.acctnbr,1,findw(atv.acctnbr,scan(atv.acctnbr,-1))-2). What i mean is how to translate substr, findw and scan in the code.

Re: Convert sas code to sql

SASKiwi — Wed, 10 Aug 2022 20:30:56 GMT

Translate into what? Does it work correctly already?

Re: Convert sas code to sql

skhan2 — Wed, 10 Aug 2022 20:36:02 GMT

Translate to sql code. It works perfectly in SAS. Wanted to check this in sql. I could not find any code for SCAN, findw in sql. So if you can help me in providing it sql code will be very helpful.

Re: Convert sas code to sql

Kurt_Bremser — Wed, 10 Aug 2022 20:41:18 GMT

This is SQL code, so where is the need to "translate"?

Re: Convert sas code to sql

skhan2 — Wed, 10 Aug 2022 20:49:19 GMT

These functions like FindW and SCAN are SAS specific. I need to convert it to sql to run in SQL server. Basically i have converted almost all the codes to sql server from SAS, however this piece is remaining. I could not find any alternaltives in SQL for these functions, so looking for help.
How can i convert the case statement with these SAS specific codes to sql server codes is my question.

Re: Convert sas code to sql

Kurt_Bremser — Wed, 10 Aug 2022 21:08:03 GMT

You shoud have mentioned in your first post that you want to migrate code from SAS SQL to MS SQL Server.

I suggest you consult the documentation for the SAS functions (FINDW, SCAN), and the documentation of SQL Server to find the equivalent functions.

Re: Convert sas code to sql

SASKiwi — Wed, 10 Aug 2022 21:09:43 GMT

Are you going to use SQL Passthru then? That's the only way you can run SQL Server-specific code in SAS. Have you tested the SQL as it is against SQL Server to see if it works? There's no problem running SAS functions in SQL against SQL Server if you use an "implicit SQL" connection.