Help using Base SAS procedures

Repeating a process for each dataset identified

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Repeating a process for each dataset identified

 

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


Accepted Solutions
Solution
‎04-15-2017 02:51 AM
PROC Star
Posts: 283

Re: Repeating a process for each dataset identified

[ Edited ]

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


All Replies
Super User
Posts: 11,343

Re: Repeating a process for each dataset identified

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

Occasional Contributor
Posts: 10

Re: Repeating a process for each dataset identified

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

Super User
Posts: 19,805

Re: Repeating a process for each dataset identified

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. 

Solution
‎04-15-2017 02:51 AM
PROC Star
Posts: 283

Re: Repeating a process for each dataset identified

[ Edited ]

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 190 views
  • 0 likes
  • 4 in conversation