topic Re: Removing from a macro variable in SAS Programming

Removing from a macro variable

JasonNC — Wed, 02 May 2012 13:52:44 GMT

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.

Re: Removing from a macro variable

RickM — Wed, 02 May 2012 18:39:21 GMT

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!

Re: Removing from a macro variable

JasonNC — Wed, 02 May 2012 19:08:47 GMT

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.

Re: Removing from a macro variable

FloydNevseta — Wed, 02 May 2012 20:08:48 GMT

Try this:

%let group=country state city;

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

%put &group1;

Re: Removing from a macro variable

Astounding — Wed, 02 May 2012 20:11:59 GMT

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.

Re: Removing from a macro variable

RickM — Wed, 02 May 2012 20:13:49 GMT

Also

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

works.

Re: Removing from a macro variable

Ksharp — Thu, 03 May 2012 05:37:41 GMT

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