BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Logie
Fluorite | Level 6

 

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
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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

Logie
Fluorite | Level 6
Many thanks, Naveen Srinivasan
That worked, 🙏
novinosrin
Tourmaline | Level 20

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

Naveen

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 632 views
  • 0 likes
  • 2 in conversation