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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.