Breaking out the SAS macro constraints - is this possible?

[RobinL]

Hello all,

I have been given a database in which each 'root' variable has 94 variants, with suffixes running from A to CP (don't ask why - unfortunately it's not something I can change).   So for instance, there may exist a 'root' variable called "result", for which in the dataset there exist 94 fields resulta, resultb, ...., resultcp.

Very often I want to drop many of these variables, but not all.  I would like to be able to specify a range of variables to drop.  The syntax I would like would be something like: 

drop = %dropvars(result, 3,5)

which would output

drop = resultc resultd resulte

(I realise that if these variables were contiguous in the dataset, i could specify the range directly.  However,  I cannot guarantee they will be contiguous, so I need another option..)

Achieving the string "resultc resultd resulte"  in a macro variable is relatively easy.  For instance, I have a table called suffixes:

i suffix

1 a

2 b

..

94 cp

I can then just do something like:

proc sql;

select suffix

into :suffixlist separated by " "

from suffixes

where i > start and < end;

quit;

However, if this code were part of a macro it gets outputted when I call the macro.

It's therefore not possible to use the macro in code like:

data datadropped;

set database (drop = %dropvars(result, 3,5) );

run;

There are various getarounds to this problem, one of which I have successfully implemented.  Basically, this solution involves running the proc sqls first to create macro variables containing the variables to drop, and then using these macro variables in the drop statement.  However, it's nowhere near as 'clean' as the code I would like.

Is there any way around this, or is what I want to achieve simply antithetical to the SAS macro system? 

Thanks very much for any help,

Robin

[LinusH]

Since your lookup data resides in a table, it's hard for you to reach that information using an in-line macro.

One quite easy compromise would be to have your SQL executed first, without the where-clause, then have your in-line macro navigate in the resulting macro variable.

[data_null__ · Accepted solution]

Just put the lookup table in a macro variable.  It can be in the macro or global.

17         %macro dropvars(root,b,e);
18            %local suffix i;
19            %let suffix = a b c d e f g ... ac;
20            %do i = &b %to &e; &root.%scan(&suffix,&i,%str( ))%end;
21            %mend dropvars;
22         %put NOTE: %dropvars(result, 3,5);
NOTE: resultc resultd resulte

[data_null__]

This is another version that uses the suffix as the arguments not the index.

24         %macro dropvars(root,arg1,arg2);
25            %local suffix _i _w;
26            %let suffix = a b c d e f g h i j k ... ac;
27            %do _i = 1 %to 10;
28               %let _w = %scan(&suffix,&_i,%str( ));
29               %local &_w;
30               %let &_w = &_i;
31               %end;
32            %do _i = &&&arg1 %to &&&arg2; &root.%scan(&suffix,&_i,%str( ))%end;
33            %mend dropvars;
34         %put NOTE: %dropvars(result,c,i);
NOTE: resultc resultd resulte resultf resultg resulth resulti

[Tom]

It is possible to read from a dataset in a macro.  You can use %SYSCALL SET() function to link the dataset variables to macro variables so that you can fetch the observations from the table.

data suffixes;

  input i suffix $ @@;

cards;

1 a 2 b 3 c 4 d 94 cp

run;

%macro dropvars(prefix,values);

%local i suffix rc did ;

%let did=%sysfunc(open(suffixes(where=(i in (&values))))) ;

%if &did %then %do;

  %syscall set(did);

%* Note the single space between end of the do statement and macro variable expansion ;

  %do %while (not %sysfunc(fetch(&did))); &prefix.%trim(&suffix) %end;

  %let rc=%sysfunc(close(&did));

%end;

%mend dropvars;

%put %dropvars(RESULT,1 3 4);

[RobinL]

Thanks all for your input.  I've not explored macro functions in as much depth as I should have.  Once i've seen how to do it it seems obvious: if you want a sas macro that performs calculations but only outputs the results of those calculations, you need to be using macro functions rather than performing the calculations in base SAS code.