DATA Step, Macro, Functions and more

DOW LOOP to merge summary statistics

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

DOW LOOP to merge summary statistics

Another basic question. I realize I can do all this with a proc means procedure. However, I am trying to learn how to use the double DOW loop to merge summary statistics. My first loop is meant to aggregate variables by Dis PerformanceLevel. The second loop is the one that's not working. My goal with the second loop: I want to know the number of observations, per district, so I can later divide sumPL1-sumPL5 by this number to obtain a mean. Here is my code:

 

data setloop3;

do i=1 by 1 until (last.PerformanceLevel);
set setloop2;

by Dis PerformanceLevel;
sumPL1=sum(sumPL1,PL1);
sumPL2=sum(sumPL2,PL2);
sumPL3=sum(sumPL3,PL3);
sumPL4=sum(sumPL4,PL4);
sumPL5=sum(sumPL5,PL5);
end;
do i=1 by 1 until (last.District);
set setloop2;
by Dis;
count=1;
if first.Dis then total=0;
total+count;
output;
end;
run;

 

If someone could please help it would be highly appreciated.


Accepted Solutions
Solution
‎03-02-2017 01:15 PM
Super User
Posts: 5,084

Re: DOW LOOP to merge summary statistics

You're trying to do too much in a single DATA step.  Most every DOW loop will use the same groupings in both DO loops.  For example, have the top loop read all observations for a DIS PerformanceLevel combination, computing a summary statistic.  Then use the bottom DO loop to re-read the exact same observations, appending those summary statistics to every observation in the group.

 

To take an example, suppose you wanted the 5 sums appended to every observation for that DIS PerformanceLevel combination.  You could use:

 

data setloop3;

do until (last.PerformanceLevel);
   set setloop2;

   by Dis PerformanceLevel;
   sumPL1=sum(sumPL1,PL1);
   sumPL2=sum(sumPL2,PL2);
   sumPL3=sum(sumPL3,PL3);
   sumPL4=sum(sumPL4,PL4);
   sumPL5=sum(sumPL5,PL5);
end;
do until (last.PerformanceLevel);
   set setloop2;
   by Dis PerformanceLevel;
   output;
end;
run;

 

Now the 5 sum variables contain subtotals for the DIS PerformanceLevel combination, but those 5 subtotals become part of every observation.

View solution in original post


All Replies
Solution
‎03-02-2017 01:15 PM
Super User
Posts: 5,084

Re: DOW LOOP to merge summary statistics

You're trying to do too much in a single DATA step.  Most every DOW loop will use the same groupings in both DO loops.  For example, have the top loop read all observations for a DIS PerformanceLevel combination, computing a summary statistic.  Then use the bottom DO loop to re-read the exact same observations, appending those summary statistics to every observation in the group.

 

To take an example, suppose you wanted the 5 sums appended to every observation for that DIS PerformanceLevel combination.  You could use:

 

data setloop3;

do until (last.PerformanceLevel);
   set setloop2;

   by Dis PerformanceLevel;
   sumPL1=sum(sumPL1,PL1);
   sumPL2=sum(sumPL2,PL2);
   sumPL3=sum(sumPL3,PL3);
   sumPL4=sum(sumPL4,PL4);
   sumPL5=sum(sumPL5,PL5);
end;
do until (last.PerformanceLevel);
   set setloop2;
   by Dis PerformanceLevel;
   output;
end;
run;

 

Now the 5 sum variables contain subtotals for the DIS PerformanceLevel combination, but those 5 subtotals become part of every observation.

☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 114 views
  • 0 likes
  • 2 in conversation