Repeating a process for each dataset identified

[Logie]

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

[ballardw]

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

[Logie]

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]

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 · Accepted solution]

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