BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TomiKong
Fluorite | Level 6

Hi all,

%let x=a1-a10;

%let y=a6;

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

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Howles
Quartz | Level 8

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 ;

    View solution in original post

    10 REPLIES 10
    GeoffreyBrent
    Calcite | Level 5

    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.

    Astounding
    PROC Star

    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?

    TomiKong
    Fluorite | Level 6

    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.

    data_null__
    Jade | Level 19

    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;

    Howles
    Quartz | Level 8

    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 ;

    data_null__
    Jade | Level 19

    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? 

    data_null__
    Jade | Level 19

    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.

    GeoffreyBrent
    Calcite | Level 5

    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.

    Haikuo
    Onyx | Level 15

    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

    TomiKong
    Fluorite | Level 6

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

    SAS Innovate 2025: Register Now

    Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
    Sign up by Dec. 31 to get the 2024 rate of just $495.
    Register now!

    How to Concatenate Values

    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.

    SAS Training: Just a Click Away

     Ready to level-up your skills? Choose your own adventure.

    Browse our catalog!

    Discussion stats
    • 10 replies
    • 2082 views
    • 6 likes
    • 6 in conversation