topic Re: Need help converting SAS code to SQL in SAS Procedures

Need help converting SAS code to SQL

sara88 — Tue, 22 Apr 2014 13:58:56 GMT

Hi,

My name is Sara.

I am trying to convert a SAS code to SQL procedure.

Since i am not familiar with SAS can any one help me to convert the code to SQL procedure

Thanks in Advance

Re: Need help converting SAS code to SQL

jakarman — Tue, 22 Apr 2014 14:33:08 GMT

SQL is only a very small subset of SAS.

You can have SQL into SAS to other way is not easy of even not possible. If your question would be can I convert Java into SQL that is easier to see.

Re: Need help converting SAS code to SQL

sara88 — Tue, 22 Apr 2014 14:53:23 GMT

Thanks Jaap,

Yes I am getting that by looking at the code. Just trying if somebody can tell me what my code is doing.

Thanks,

Radhika.

Re: Need help converting SAS code to SQL

Reeza — Tue, 22 Apr 2014 14:55:07 GMT

Depends on why and the complexity of the issue.

I don't think anyone on here will convert a 500 line program to SQL for you. A single or few datasteps perhaps.

Also, are you moving to PROC SQL or SQL in a database and bypassing SAS entirely?

And no one can help if you don't post the code .

Re: Need help converting SAS code to SQL

sara88 — Tue, 22 Apr 2014 15:16:05 GMT

Thanks Reeza,

Here is my code.

Source files: tbsisstu, tbsisenrl, and tb_cds_mst

--- Student File

select * from sis..tbsisstu

order by intsystemstuid

---- Enrollment file

set nocount on

Select *

from sis..tbsisenrl

where chrSchYr=2014

order by intsystemstuid

Import both files to my U drive

For enrollment file,

Here are the SAS codes

format rcdts $15. rcdt $11.;

rcdts=upcase(chrRCDTSServing) ;

rcdt=substr(rcdts,1,11);

 Use serving unless type deigns with ‘4’

if substr(rcdts,10,1) = '4' then rcdts=upcase(chrRCDTSHome);

 Omit birth to three

if dtmEnrlDate<'2012-10-01 00:00:00.000' and

(dtmExitDate>='2012-09-30 00:00:00.000' or dtmExitDate='NULL') ;

if intEnrlExitType NE '23';

if intEntryGrdId=2 then delete;

cnt=1;

 Omit districts that end in XYZPS for region and county not sevens

if substr(rcdts,1,5) NE '77777';

if substr(rcdts,9,1) in ('X','Y','Z','P','S') then delete;

format rcdt $11.;

rcdt=substr(rcdts,1,11);cnt=1;

dty=substr(rcdts,10,1);

proc sort data=enr;by descending decFTE descending intEnrlId;

proc sort nodupkey;by intSystemStuId ;run;

Then merge both file together by intSystemStuId.

data demox1;merge demo enr(in=x);by intSystemStuId;if x;

format grade $2.;

if intEntryGrdId=3 then grade='PK';

if intEntryGrdId=4 then grade='KG';

if intEntryGrdId=5 then grade='01';

if intEntryGrdId=6 then grade='02';

if intEntryGrdId=7 then grade='03';

if intEntryGrdId=8 then grade='04';

if intEntryGrdId=9 then grade='05';

if intEntryGrdId=10 then grade='06';

if intEntryGrdId=11 then grade='07';

if intEntryGrdId=12 then grade='08';

if intEntryGrdId=13 then grade='09';

if intEntryGrdId=14 then grade='10';

if intEntryGrdId=15 then grade='11';

if intEntryGrdId=16 then grade='12';

if intEntryGrdId=. then grade='UG';

proc sort;by rcdts;

input EPS file,

DATA CDSfile;

infile 'u:\cdsfile\CDS_rc13.txt' delimiter = '|' dsd missover pad lrecl=9327 firstobs=2 ;

format RCD $9. Sch $4. ff $2. Cat $1. DTYPE $2. reg $ 1. ADMIN $33. sNAME $40.

ADDRESS $20. CITY $20. STATE $2. ZIP $8. PHONE $11. xx1-xx11 $8. cong $2.

xx13-xx16 $3. enr_cds 7. ypk $1. yk $1. yg1-yg12 $1. y1-y5 $1. fax $15. z1-z2 $2.

email $55.;

input RCD $ Sch $ ff $ CAT $ DTYPE $ reg $ ADMIN $ sNAME $ ADDRESS $ city $

STATE $ ZIP $ PHONE $ xx1-xx11 $ cong $ xx13-xx16 $ enr_cds ypk $ yk $ yg1-yg12 $

y1-y5 $ fax $ z1-z2 $ email $;

format rcdt $11. rcdts $15.;

rcdt=rcd||dtype;

rcdts=rcdt||sch;

if cat in ('C','E','5',' ','D') then delete;

proc sort;by rcdts;

Merge EPS file with student file and remove the enrollment from category C, E, 5, and D.

data demox2;merge cdsfile(in=x) demox1(in=y);

by rcdts;if x and y;

Calculate the counts by grade, gender, and race.

Re: Need help converting SAS code to SQL

Reeza — Tue, 22 Apr 2014 15:18:50 GMT

Are you moving to SAS SQL or doing this on a database?

Re: Need help converting SAS code to SQL

sara88 — Tue, 22 Apr 2014 15:19:45 GMT

I am doing this in a database

Re: Need help converting SAS code to SQL

Reeza — Tue, 22 Apr 2014 15:23:08 GMT

Then as a personal rule, I generally don't help with those questions on here, others will.

Consider this step in the SAS process though. That's a data import step, if you're in SQL server you'll need to build an SSIS job for that, this won't be a straight conversion from SAS code to SQL code.

Good Luck.

DATA CDSfile;

infile 'u:\cdsfile\CDS_rc13.txt' delimiter = '|' dsd missover pad lrecl=9327 firstobs=2 ;

format RCD $9. Sch $4. ff $2. Cat $1. DTYPE $2. reg $ 1. ADMIN $33. sNAME $40.

ADDRESS $20. CITY $20. STATE $2. ZIP $8. PHONE $11. xx1-xx11 $8. cong $2.

xx13-xx16 $3. enr_cds 7. ypk $1. yk $1. yg1-yg12 $1. y1-y5 $1. fax $15. z1-z2 $2.

email $55.;

input RCD $ Sch $ ff $ CAT $ DTYPE $ reg $ ADMIN $ sNAME $ ADDRESS $ city $

STATE $ ZIP $ PHONE $ xx1-xx11 $ cong $ xx13-xx16 $ enr_cds ypk $ yk $ yg1-yg12 $

y1-y5 $ fax $ z1-z2 $ email $;

format rcdt $11. rcdts $15.;

rcdt=rcd||dtype;

rcdts=rcdt||sch;

if cat in ('C','E','5',' ','D') then delete;

Re: Need help converting SAS code to SQL

sara88 — Tue, 22 Apr 2014 15:38:02 GMT

Thank you Reeza. I will try this in SSIS