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
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.
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.
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 .
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.
Are you moving to SAS SQL or doing this on a database?
I am doing this in a database
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;
Thank you Reeza. I will try this in SSIS
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.