topic Re: Repeating a processs for each dataset identified - part 2 in SAS Programming

Repeating a processs for each dataset identified - part 2

Logie — Sun, 16 Apr 2017 05:31:01 GMT

THANK YOU IN ADVANCE

This is an extention of the problem posted a few days ago.

I need help to generate individual school reports from a master schools table (A) and append national averages. The addition requirement is data for each school, and the national averages, are reported over several years.

Thank you

Have I a master schools table (A) with annual, individual school records and national averages

Year	School ID	Enrolments	%Females	%LowSES	%Black	%Secondary_Education
2002	1	200	50	12	15	59
2003	1	111	100	80	80	100
2004	1	80	0	15	26	25
2005	1	20	100	25	3	59
2001	2	80	40	34	5	90
2002	2	55	50	34	64	64
2003	2	45	46	56	76	86
2004	2	25	56	78	42	38
2005	2	20	20	29	78	98
2001	3	80	60	55	18	56
2002	3	266	50	65	20	50
2003	3	300	100	48	45	50
2004	3	800	0	67	67	50
2005	3	1,000	100	63	58	45
2001	All	160	50	45	12	73
2002	All	521	50	37	33	58
2003	All	456	82	61	67	79
2004	All	905	19	53	45	38
2005	All	1,040	73	39	46	67

I want three separate tables, one for each school:

Year	School ID	Enrolments	%Females	%LowSES	%Black	%Secondary_Education
2002	1	200	50	12	15	59
2003	1	111	100	80	80	100
2004	1	80	0	15	26	25
2005	1	20	100	25	3	59
2002	All	521	50	37	33	58
2003	All	456	82	61	67	79
2004	All	905	19	53	45	38
2005	All	1,040	73	39	46	67


Year	School ID	Enrolments	%Females	%LowSES	%Black	%Secondary_Education
2001	2	80	40	34	5	90
2002	2	55	50	34	64	64
2003	2	45	46	56	76	86
2004	2	25	56	78	42	38
2005	2	20	20	29	78	98
2001	All	160	50	45	12	73
2002	All	521	50	37	33	58
2003	All	456	82	61	67	79
2004	All	905	19	53	45	38
2005	All	1,040	73	39	46	67


Year	School ID	Enrolments	%Females	%LowSES	%Black	%Secondary_Education
2001	3	80	60	55	18	56
2002	3	266	50	65	20	50
2003	3	300	100	48	45	50
2004	3	800	0	67	67	50
2005	3	1,000	100	63	58	45
2001	All	160	50	45	12	73
2002	All	521	50	37	33	58
2003	All	456	82	61	67	79
2004	All	905	19	53	45	38
2005	All	1,040	73	39	46	67

Re: Repeating a processs for each dataset identified - part 2

novinosrin — Sun, 16 Apr 2017 06:57:19 GMT

data have;

input Year School_ID $ Enrolments Females LowSES Black Secondary_Education ;

datalines;

2002 1 200 50 12 15 59

2003 1 111 100 80 80 100

2004 1 80 0 15 26 25

2005 1 20 100 25 3 59

2001 2 80 40 34 5 90

2002 2 55 50 34 64 64

2003 2 45 46 56 76 86

2004 2 25 56 78 42 38

2005 2 20 20 29 78 98

2001 3 80 60 55 18 56

2002 3 266 50 65 20 50

2003 3 300 100 48 45 50

2004 3 800 0 67 67 50

2005 3 1000 100 63 58 45

2001 All 160 50 45 12 73

2002 All 521 50 37 33 58

2003 All 456 82 61 67 79

2004 All 905 19 53 45 38

2005 All 1040 73 39 46 67

;

/*solution*/

data _null_;

if (_n_ = 1) then do;

if 0 then set have;

declare hash myhash(multidata:'YES');

rc = myhash.definekey('School_ID');

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

myhash.definedone();

declare hash myhash2(dataset: "have(where=(School_ID='All'))");

rc = myhash2.definekey('Year');

myhash2.definedata(ALL: 'YES');

myhash2.definedone();

end;

set have end=last;

by school_id;

retain _school_id;

if first.school_id then do;

rc=myhash.clear();

_school_id=school_id;

end;

rc=myhash.add();

rc=myhash2.find(key:year);

if rc=0 then rc=myhash.add();

if last.school_id then rc=myhash.output(dataset:'school'||_school_id);

drop _school_id rc;

run;

Regards,

Naveen Srinivasan

Re: Repeating a processs for each dataset identified - part 2

Logie — Sun, 16 Apr 2017 08:22:01 GMT

Many thanks, Naveen Srinivasan
That worked, 🙏

Re: Repeating a processs for each dataset identified - part 2

novinosrin — Sun, 16 Apr 2017 09:01:07 GMT

You are most welcome. Have a great weekend! Cheers,

Naveen