BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS_inquisitive
Lapis Lazuli | Level 10

 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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

6 REPLIES 6
Astounding
PROC Star

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.

SAS_inquisitive
Lapis Lazuli | Level 10

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

FreelanceReinh
Jade | Level 19

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

SAS_inquisitive
Lapis Lazuli | Level 10
@ FreelanceReinhard. Thank you for these references.
SAS_inquisitive
Lapis Lazuli | Level 10

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;
Astounding
PROC Star

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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