Hi,
I am having a macro variable
%let group=country state city county
for each iteration i need to remove the last one and use it as a group item
i have to 3 iterations
when i=1
&group=country state city
proc sql ;
creat table test1
select *,sum(pop)
from tes1
group by &group
when i=2
&group=country state
I want create a macro variable like that.i want to use that macro variable in grouping sql.
i tried using %scan but i am duno know how to handle the remaining value of the string.
Can some one help me out.
Hi Jason,
You can try using the following code.
%let vars=country state city county;
%do i=1 %to 3;
%let group=%substr(&vars,1,%eval( %index(&vars, %scan(&vars,%eval(5-&i)) )-1) );
%put &group;
%end;
The nested functions can be a little hard to follow. Using another %do loop and concatenating the variables(%let group=&group %scan(&vars,&j) ) might be a better option if you want to improve readability of the program.
Good luck!
Hi RIck,
Thanks for replying.I got it .Anways i did the same way nested code.previously i did a small mistake.
i am trying to insert ,using tranwrd function but it is not working in %sysfunc
%let group1=%sysfunc(tranwrd("&group",' ' , ',');
it is not inserting , between the values.
Try this:
%let group=country state city;
%let group1=%sysfunc( tranwrd( &group, %str( ),%str(,) ));
%put &group1;
Also
%let group1=%sysfunc( translate( &group, ',' , ' ' ));
works.
Jason,
Perhaps it is possible that you are solving this problem the wrong way. You can get the sums of POP at various levels in one step:
proc summary data=tes1;
class state county city;
var pop;
output out=test1 sum=;
run;
You can use the ID statement to bring in other variables if needed, and another statement that I forget off the top of my head to obtain just some of the levels of summarization instead of all.
At any rate, PROC SUMMARY with a CLASS statement is definitely a tool you should have in your arsenal.
Good lucki.
How about :
%macro test; %let group=country state city county; %put &group; %let i=%sysfunc(countw(&group)); %let temp=%scan(&group,&i); %let group=%sysfunc(tranwrd(&group,&temp,%str( ))); %do %while(&i ge 2); %put &group; %let i=%eval(&i-1); %let temp=%scan(&group,&i); %let group=%sysfunc(tranwrd(&group,&temp,%str( ))); %end; %mend test; %test
Ksharp
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.