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;
/*
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;
/*
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;
Thank you. It was the data want&i; part that I forgot. Thank you!
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
@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?
Okay, thanks again!
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;
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?
To find the MAX (or many other statistics) use PROC SUMMARY. I posted example already.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.