topic Repeating a process for each dataset identified in SAS Procedures

Repeating a process for each dataset identified

Logie — Fri, 14 Apr 2017 22:46:04 GMT

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

Re: Repeating a process for each dataset identified

ballardw — Fri, 14 Apr 2017 23:00:13 GMT

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

Re: Repeating a process for each dataset identified

Logie — Fri, 14 Apr 2017 23:03:26 GMT

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

Re: Repeating a process for each dataset identified

Reeza — Fri, 14 Apr 2017 23:27:33 GMT

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.

Re: Repeating a process for each dataset identified

novinosrin — Sat, 15 Apr 2017 05:46:02 GMT

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