Removing from a macro variable

[JasonNC]

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.

[RickM]

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!

[JasonNC]

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.

[FloydNevseta]

Try this:

%let group=country state city;

%let group1=%sysfunc( tranwrd( &group, %str( ),%str(,) ));

%put &group1;

[RickM]

Also

%let group1=%sysfunc( translate( &group, ',' , ' ' ));

works.

[Astounding]

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.

[Ksharp]

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