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