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;
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.
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.
@Astounding. Thank you. I was just experimenting how does double DOW LOOP works. What are the circumstances that it is best suited to?
Hi @SAS_inquisitive,
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."
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;
This works as long as you have no missing values for AGE. If there are missing values, COUNT will be wrong.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.