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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.