BookmarkSubscribeRSS Feed
sara88
Calcite | Level 5

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

8 REPLIES 8
jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
sara88
Calcite | Level 5

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.

Reeza
Super User

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

sara88
Calcite | Level 5

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.

Reeza
Super User

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

sara88
Calcite | Level 5

I am doing this in a database

Reeza
Super User

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;

sara88
Calcite | Level 5

Thank you Reeza. I will try this in SSIS

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1530 views
  • 0 likes
  • 3 in conversation