topic DOW LOOP to merge summary statistics in SAS Programming

DOW LOOP to merge summary statistics

SAS_inquisitive — Fri, 04 Mar 2016 16:29:47 GMT

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;

Re: DOW LOOP to merge summary statistics

Astounding — Fri, 04 Mar 2016 15:44:47 GMT

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.

Re: DOW LOOP to merge summary statistics

SAS_inquisitive — Fri, 04 Mar 2016 16:02:37 GMT

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

Re: DOW LOOP to merge summary statistics

SAS_inquisitive — Fri, 04 Mar 2016 16:24:40 GMT

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;

Re: DOW LOOP to merge summary statistics

FreelanceReinh — Fri, 04 Mar 2016 16:31:45 GMT

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

Re: DOW LOOP to merge summary statistics

SAS_inquisitive — Fri, 04 Mar 2016 16:36:36 GMT

@ FreelanceReinhard. Thank you for these references.

Re: DOW LOOP to merge summary statistics

Astounding — Fri, 04 Mar 2016 17:56:35 GMT

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