Macro with DO UNTIL

[eabc0351]

Hello. Please see below code. I need to run this on 20 variables, from Yr1 to Yr20. Tried to make this into a macro, but it didn't work. Is it possible to turn this into a macro so it doesn't require re-running it for all 20 variables.  Thank you.

data want;
do until (last.ID);
	set have;
	by ID;
	highest=max(highest, Yr1);
end;
do until (last.ID);
	set have;
	by ID;
	Yr1=highest;
output;
end;
drop highest;
run;

[ghosh · Accepted solution]

/* 
wrap your code inside this macro do loop.
Make sure you replace Yr1 to Yr&i  
and data want to 
data want&i; 

*/

%macro run20x;
%do i=1 %to 20;

* put your code here ;

%end;
%mend;

%run20x;

[eabc0351]

Thank you. It was the data want&i; part that I forgot. Thank you!

[ghosh]

You're welcome, but I would suggest you re-think your approach.  You many not need 20 variables.  Also use the power of SAS to find max values, perhaps Proc Summary

[eabc0351]

@ghoshthanks, will consider that. One more question. The macro creates 20 datasets from want&i. Is there anyway not to create 20, but to rewrite over the same one - similar to a datastep?

[ghosh]

Well if you just use "data want", it will overwrite.

But I am sure that's not what you want. Better to create an ID variable and increment it for each iteration.

[eabc0351]

Okay, thanks again!

[Tom]

What are you trying to create?

Are you just trying to find the maximum value for 20 variables?

proc summary data=have;
  by id;
  var yr1-yr20;
  output out=want max=;
run;

[eabc0351]

Hi @Tom. Here's what I'm trying to do with the DO UNTIL code.

data have
input id$2. Yr1 Yr2 ... Yr20 ;
datalines;
1 4 . ... 3
1 . 2 ... .
1 . . ... .      
2 . . ... 2
2 1 . ... .
3 . . ... .
3 . 3 ... 4
4 . 4 ... .
4 . . ... .
4 2 . ... .
4 . . ... .
;

data want
input id$2. Yr1 Yr2 ... Yr20 ;
datalines;
1 4 2 ... 3     
2 1 . ... 2
3 . 3 ... 4
4 2 4 ... .
;

I get data want through two steps, which are:
1) Use DO UNTIL to replace all values within ID by group to highest value. For example:
datastep1
input id$2. Yr1 Yr2 ... Yr20 ;
datalines;
1 4 2 ... 3
1 4 2 ... 3
1 4 2 ... 3
2 1 . ... 2
2 1 . ... 2
run;
2) Then collapse on id to get data want

If there is a better way to do this, I'm all ears. Maybe proc sql select case when?

[Tom]

To find the MAX (or many other statistics) use PROC SUMMARY.  I posted example already.

[eabc0351]

Thanks to you both @ghosh @Tom. This will save much time.