DATA Step, Macro, Functions and more

Repeating a processs for each dataset identified - part 2

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Repeating a processs for each dataset identified - part 2

 

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

Accepted Solutions
Solution
‎04-16-2017 04:21 AM
PROC Star
Posts: 162

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

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


All Replies
Solution
‎04-16-2017 04:21 AM
PROC Star
Posts: 162

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

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

Occasional Contributor
Posts: 10

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

Many thanks, Naveen Srinivasan
That worked, 🙏
PROC Star
Posts: 162

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

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

Naveen

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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