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
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
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
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;
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.
Translate into what? Does it work correctly already?
This is SQL code, so where is the need to "translate"?
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.
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.