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 |
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
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
You are most welcome. Have a great weekend! Cheers,
Naveen
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.