BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Logie
Fluorite | Level 6

 

THANK YOU in advance.

 

The task is to create a SAS / SQL program to:

(a) sequentially read in school ids from the master schools table (A) to create a lookup list;

(b) then use the list of school ids to create separate tables for each school, and append national average.

 

 

 Have: master schools table (A) with individual school records and national averages, (i.e. All):

School ID Enrolments  %Females %LowSES %Black %Secondary_Education
1 30 60 33 0 80
2 200 50 12 15 59
3 111 100 80 80 100
4 80 0 15 26 25
5 20 100 25 3 59
All 441 65 33 25 65

 

Want five separate tables, one for each school:

School ID

Enrolments

%Females

%LowSES

%Black

%Secondary_Education

1

30

60

33

0

80

All

441

65

33

25

65

           
           

School ID

Enrolments

%Females

%LowSES

%Black

%Secondary_Education

2

200

50

12

15

59

All

441

65

33

25

65

           
           

School ID

Enrolments

%Females

%LowSES

%Black

%Secondary_Education

3

111

100

80

80

100

All

441

65

33

25

65

           
           

School ID

Enrolments

%Females

%LowSES

%Black

%Secondary_Education

4

80

0

15

26

25

All

441

65

33

25

65

           
           

School ID

Enrolments

%Females

%LowSES

%Black

%Secondary_Education

5

20

100

25

3

59

All

441

65

33

25

65

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

Hi, I hope this meets your requirement:

 

 

data have;

input School_ID $    Enrolments      Females    LowSES     Black Secondary_Education ;

datalines;

1    30   60   33   0    80

2    200  50   12   15   59

3    111  100  80   80   100

4    80   0    15   26   25

5    20   100  25   3    59

All  441  65   33   25   65

;

/*Solution*/

 

data _null_;

if (_n_ = 1) then do;

if 0 then set have;

declare hash myhash();

rc = myhash.definekey('School_ID');

myhash.definedata('School_ID','Enrolments','Females','LowSES','Black','Secondary_Education');

myhash.definedone();

end;

set have end=last;

rc=myhash.clear();

if _n_=1 then set have( where=( _School_ID='All') rename=(School_ID=_School_ID Enrolments=_Enrolments Females=_Females LowSES=_LowSES Black=_Black

Secondary_Education=_Secondary_Education ));

array s(*) Enrolments--Secondary_Education;

array _s(*) _Enrolments--_Secondary_Education;

rc=myhash.add();

School_ID=_School_ID;

do i=1 to dim(s);

s(i)=_s(i);

end;

rc=myhash.add();

if not last then rc=myhash.output(dataset:'school'||left(_n_));

drop rc _:;

run;

 

Regards,

Naveen Srinivasan

View solution in original post

4 REPLIES 4
ballardw
Super User

Do you already know how to create any of those tables?

Logie
Fluorite | Level 6

the data already exists in the master school table (A)

School ID Enrolments  %Females %LowSES %Black %Secondary_Education
1 30 60 33 0 80
2 200 50 12 15 59
3 111 100 80 80 100
4 80 0 15 26 25
5 20 100 25 3 59
All 441 65 33 25 65

 

Thank you

Reeza
Super User

Are you creating each dataset for a report? Because it's better to use a report procedure instead of multiple tables. 

 

And you've outlined what you need to do but not where exactly you need help. 

novinosrin
Tourmaline | Level 20

Hi, I hope this meets your requirement:

 

 

data have;

input School_ID $    Enrolments      Females    LowSES     Black Secondary_Education ;

datalines;

1    30   60   33   0    80

2    200  50   12   15   59

3    111  100  80   80   100

4    80   0    15   26   25

5    20   100  25   3    59

All  441  65   33   25   65

;

/*Solution*/

 

data _null_;

if (_n_ = 1) then do;

if 0 then set have;

declare hash myhash();

rc = myhash.definekey('School_ID');

myhash.definedata('School_ID','Enrolments','Females','LowSES','Black','Secondary_Education');

myhash.definedone();

end;

set have end=last;

rc=myhash.clear();

if _n_=1 then set have( where=( _School_ID='All') rename=(School_ID=_School_ID Enrolments=_Enrolments Females=_Females LowSES=_LowSES Black=_Black

Secondary_Education=_Secondary_Education ));

array s(*) Enrolments--Secondary_Education;

array _s(*) _Enrolments--_Secondary_Education;

rc=myhash.add();

School_ID=_School_ID;

do i=1 to dim(s);

s(i)=_s(i);

end;

rc=myhash.add();

if not last then rc=myhash.output(dataset:'school'||left(_n_));

drop rc _:;

run;

 

Regards,

Naveen Srinivasan

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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
  • 4 replies
  • 830 views
  • 0 likes
  • 4 in conversation