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. ... View more

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. ... View more

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. ... View more

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; ... View more