05-02-2012 09:52 AM
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
&group=country state city
proc sql ;
creat table test1
group by &group
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.
05-02-2012 02:39 PM
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) );
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.
05-02-2012 03:08 PM
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.
05-02-2012 04:11 PM
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;
output out=test1 sum=;
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.
05-03-2012 01:37 AM
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