topic Re: how to delete one macro variable for another macro variable? in SAS Programming

how to delete one macro variable for another macro variable?

TomiKong — Fri, 31 Aug 2012 04:42:42 GMT

Hi all,

%let x=a1-a10;

%let y=a6;

how to delete &y (a6) from &x efficiently?

Thanks.

Re: how to delete one macro variable for another macro variable?

GeoffreyBrent — Fri, 31 Aug 2012 06:07:50 GMT

You can't delete it from x, because it's not in x to begin with.

What you've done here is define &x as the literal string "a1-a10". I assuming you're thinking of this as a list of variables a1, a2, ..., a10, but when you define "%let x=a1-a10" SAS doesn't perform that interpretation. SAS will substitute the characters "a1-a10" in the code where "&x" appears and only then will it interpret it.

Demonstration:

%let x=a1-a3;

data test;

a1=5;

a2=6;

a3=7;

diff=&x;

sum=sum(of &x);

run;

When you run this code, the first thing SAS does is to substitute "a1-a3" where "&x" appears:

data test;

a1=5;

a2=6;

a3=7;

diff=a1-a3;

sum=sum(of a1-a3);

run;

It then interprets based on context. In the first case, it interprets "a1-a3" as a1 minus a3 (= 5-7 = -2); in the second case it interprets it as "a1, a2, a3" (= 5+6+7 = 18).

If you can give some more context on what you're looking to do, people might be able to suggest some other solution.

Re: how to delete one macro variable for another macro variable?

Astounding — Fri, 31 Aug 2012 14:14:23 GMT

The first answer you got was correct. But there may be ways to program around it. It all depends on what you need your application to do. Here is one possible workaround, for one particular application:

total = sum(of &x, -&y);

There are also ways to get macro language to parse the variable list into individual names. After that is done, it would be possible to remove &y from that list of individual names. But as the first reply noted, give us more context. How will you be using &x?

Re: how to delete one macro variable for another macro variable?

TomiKong — Fri, 31 Aug 2012 16:15:59 GMT

Hi Geoffrey, Astounding,

Thanks a lot for your kind reply.

X (a1-a10) are independent variable candidates for regression. Miss rate will be calculated for each of them. Then I will remove these with high miss rate from X.

I want to write one macro to do this automatically. But I still have no clue to remove variable with high miss rate from list...

Thanks again.

Re: how to delete one macro variable for another macro variable?

data_null__ — Fri, 31 Aug 2012 16:33:36 GMT

This may be something like you are looking for. Each list X and Y are expanded and then any name in Y is removed from X and a new macro variable is created

NOTE: NEWX=a1 a2 a3 a4 a5 A7 A8 A9 A10

This will work for any type of "SAS Variable List" as long as the variables exist in a data set.

data test;
   length a1-a5 A6-A12 8.;
   stop;
   call missing(of _all_);
   run;

%let x=a1-a10;%let y=a6;

proc transpose data=test(obs=0) out=x;
   var &x;
   run;

proc transpose data=test(obs=0) out=y;
   var &y;
   run;
proc sql noprint;
   select _name_ into :newX separated by ' '
      from X
      where _name_ ne ALL(select _name_ from Y);
   quit;
   run;

%put NOTE: NEWX=&newX;

Re: how to delete one macro variable for another macro variable?

Howles — Fri, 31 Aug 2012 21:08:40 GMT

Or ...

%let x=a01-a10;

%let y=a06;

data xx yy ;

keep vn ;

array xxx

&x ;

array yyy

&y ;

do i = 1 to dim(xxx) ; vn = vname(xxx) ; output xx ; end ;

do i = 1 to dim(yyy) ; vn = vname(yyy) ; output yy ; end ;

run ;

proc sql noprint ;

select vn into : z separated by ' '

from ( select vn from xx

except

select vn from yy ) ;

drop table xx, yy ;

quit ;

%put &z ;

Re: how to delete one macro variable for another macro variable?

Haikuo — Fri, 31 Aug 2012 22:39:42 GMT

If you only need to take out one variable name at a time, like your sample showed, you could just use the following simpler approach:

%let x=a1 a2 a3; %let y=a1;

data _null_;

new_x=tranwrd("&x","&y",'');

call symputx('x',new_x);

run;

%put &x;

If you have more than one variable names needs to be taken off the parent list, then you already have good answers above or use a little loop:

%let x=a1 a2 a3; %let y=a1 a3;

data _null_;

x="&x"; y="&y";

do i=1 by 1 to countw(y);

_y=scan(y,i);

x=tranwrd(x,trim(_y),'');

end;

call symputx('x',x);

run;

%put &x;

Haikuo

Re: how to delete one macro variable for another macro variable?

data_null__ — Fri, 31 Aug 2012 22:43:31 GMT

That's all good except it only works for enumerated variable lists.

I would like to know about my SQL SELECT since I got "NE ALL" from your book why you chose to use EXCEPT, is one better than the other?

Re: how to delete one macro variable for another macro variable?

data_null__ — Fri, 31 Aug 2012 22:53:09 GMT

Maybe you need to show your work.

How do you calculate miss rate?

You may not have to REMOVE anything but just gen a new list of variables that have acceptable miss rate.

Re: how to delete one macro variable for another macro variable?

TomiKong — Sat, 01 Sep 2012 08:28:06 GMT

Awesome. All works fine. Thank you all so much.

Re: how to delete one macro variable for another macro variable?

GeoffreyBrent — Sun, 02 Sep 2012 23:56:36 GMT

I see you already have solutions, but you might (or might not) find that existing SAS regression options can save you the need to write your own macro.

For instance, PROC REG gives you options for stepwise selection (add variables with best explanatory power/remove variables with worst) or by using something like SELECTION=MAXR STOP=&MAXEXPVARS you can force SAS to choose the best n variables.