Convert sas code to sql

[chithra]

 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

[yabwon]

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

[Kurt_Bremser]

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

---

[skhan2]

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;

[SASKiwi]

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.

[skhan2]

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.

[SASKiwi]

Translate into what? Does it work correctly already?

[skhan2]

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.

[Kurt_Bremser]

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

[skhan2]

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.

[Kurt_Bremser]

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.

[SASKiwi]

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.