topic Re: Macro with DO UNTIL in SAS Programming

Macro with DO UNTIL

eabc0351 — Thu, 04 Apr 2019 01:05:38 GMT

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;

Re: Macro with DO UNTIL

ghosh — Thu, 04 Apr 2019 01:17:48 GMT

/* 
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;

Re: Macro with DO UNTIL

eabc0351 — Thu, 04 Apr 2019 01:23:39 GMT

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

Re: Macro with DO UNTIL

ghosh — Thu, 04 Apr 2019 01:35:42 GMT

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

Re: Macro with DO UNTIL

eabc0351 — Thu, 04 Apr 2019 01:56:54 GMT

@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?

Re: Macro with DO UNTIL

ghosh — Thu, 04 Apr 2019 02:09:55 GMT

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.

Re: Macro with DO UNTIL

eabc0351 — Thu, 04 Apr 2019 02:17:37 GMT

Okay, thanks again!

Re: Macro with DO UNTIL

Tom — Thu, 04 Apr 2019 02:57:02 GMT

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;

Re: Macro with DO UNTIL

eabc0351 — Thu, 04 Apr 2019 10:18:47 GMT

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?

Re: Macro with DO UNTIL

Tom — Thu, 04 Apr 2019 12:38:16 GMT

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

Re: Macro with DO UNTIL

eabc0351 — Thu, 04 Apr 2019 13:17:27 GMT

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