DATA Step, Macro, Functions and more

DOW LOOP to merge summary statistics

Accepted Solution Solved
Reply
Regular Contributor
Posts: 234
Accepted Solution

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: 5,091

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.

View solution in original post


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

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.

Regular Contributor
Posts: 234

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?

Trusted Advisor
Posts: 1,115

Re: DOW LOOP to merge summary statistics

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."

Regular Contributor
Posts: 234

Re: DOW LOOP to merge summary statistics

@ FreelanceReinhard. Thank you for these references.
Regular Contributor
Posts: 234

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: 5,091

Re: DOW LOOP to merge summary statistics

This works as long as you have no missing values for AGE.  If there are missing values, COUNT will be wrong.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 346 views
  • 3 likes
  • 3 in conversation