BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eabc0351
Quartz | Level 8

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;
1 ACCEPTED SOLUTION

Accepted Solutions
ghosh
Barite | Level 11
/* 
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;

View solution in original post

10 REPLIES 10
ghosh
Barite | Level 11
/* 
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
Quartz | Level 8

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

ghosh
Barite | Level 11

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
Quartz | Level 8

@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
Barite | Level 11
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
Quartz | Level 8

Okay, thanks again!

Tom
Super User Tom
Super User

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
Quartz | Level 8

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
Super User Tom
Super User

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

eabc0351
Quartz | Level 8

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 10 replies
  • 1314 views
  • 0 likes
  • 3 in conversation