turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- DOW LOOP to merge summary statistics

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-02-2017 11:32 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-02-2017 12:22 PM

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.

All Replies

Solution

03-02-2017
01:15 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-02-2017 12:22 PM

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.