## DOW LOOP to merge summary statistics

Solved
Super Contributor
Posts: 285

# DOW LOOP to merge summary statistics

[ Edited ]

How can get the outptut (remerging summary statistics to the original data) as from PROC SQL code using DOUBLE DOW LOOP (pseudo code below)?

``````proc sql;
select name,sex,age,height,weight,avg(age) as avg_age
from sashelp.class
group by sex;
quit;

data test;
do until(last.sex);
set sashelp.class;
by sex notsorted;
end;

/**** some code to get avergae age ****/

do until(last.sex);
set sashelp.class;
by sex notsorted;
output;
end;
run;``````

Accepted Solutions
Solution
‎03-04-2016 11:54 AM
Super User
Posts: 6,762

## Re: DOW LOOP to merge summary statistics

A couple of tricky points along the way:

```data test;        numerator=0;        denominator=0;
do until(last.sex);
set sashelp.class;
by sex notsorted;                if age > .Z then do;                   numerator + age;                   denominator + 1;                end;
end;
if denominator then av_age = numerator / denominator;
/**** some code to get avergae age ****/

do until(last.sex);
set sashelp.class;
by sex notsorted;
output;
end;
run;```

Calculations of average age need to exclude missing values.

Of course, DOW isn't the recommended approach for this problem.  But that's how to do it.

In some circumstances (not here), you would have to pay attention to the possibility of an AGE value that exists in one loop but not the other.

All Replies
Solution
‎03-04-2016 11:54 AM
Super User
Posts: 6,762

## Re: DOW LOOP to merge summary statistics

A couple of tricky points along the way:

```data test;        numerator=0;        denominator=0;
do until(last.sex);
set sashelp.class;
by sex notsorted;                if age > .Z then do;                   numerator + age;                   denominator + 1;                end;
end;
if denominator then av_age = numerator / denominator;
/**** some code to get avergae age ****/

do until(last.sex);
set sashelp.class;
by sex notsorted;
output;
end;
run;```

Calculations of average age need to exclude missing values.

Of course, DOW isn't the recommended approach for this problem.  But that's how to do it.

In some circumstances (not here), you would have to pay attention to the possibility of an AGE value that exists in one loop but not the other.

Super Contributor
Posts: 285

## Re: DOW LOOP to merge summary statistics

@Astounding.  Thank you. I was just experimenting how does double DOW LOOP works. What are the circumstances that it is best suited to?

Posts: 1,246

## Re: DOW LOOP to merge summary statistics

You can find examples and explanations in various papers:

"The DOW (not that DOW!!!) and the LOCF in Clinical Trials" explains the basics. Further examples from the pharmaceutical area can be found in "Practical Uses of the DOW Loop in Pharmaceutical Programming." Technically more advanced is "The DOW-Loop Unrolled."

Super Contributor
Posts: 285

## Re: DOW LOOP to merge summary statistics

@ FreelanceReinhard. Thank you for these references.
Super Contributor
Posts: 285

## Re: DOW LOOP to merge summary statistics

Looks like this works as well.

``````data test;
do until(last.sex);
set sashelp.class;
by sex notsorted;
count = sum (count, 1);
sum_age = sum (sum_age, age);
end;

avg_age=sum_age/count;

do until(last.sex);
set sashelp.class;
by sex notsorted;
output;
end;
run;``````
Super User
Posts: 6,762