DATA Step, Macro, Functions and more

Breaking out the SAS macro constraints - is this possible?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Breaking out the SAS macro constraints - is this possible?

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


Accepted Solutions
Solution
‎04-06-2013 08:40 AM
Respected Advisor
Posts: 3,777

Re: Breaking out the SAS macro constraints - is this possible?

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

View solution in original post


All Replies
Super User
Posts: 5,257

Re: Breaking out the SAS macro constraints - is this possible?

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 never sleeps
Solution
‎04-06-2013 08:40 AM
Respected Advisor
Posts: 3,777

Re: Breaking out the SAS macro constraints - is this possible?

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
Respected Advisor
Posts: 3,777

Re: Breaking out the SAS macro constraints - is this possible?

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
Super User
Super User
Posts: 6,502

Re: Breaking out the SAS macro constraints - is this possible?

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);

Occasional Contributor
Posts: 6

Re: Breaking out the SAS macro constraints - is this possible?

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 270 views
  • 7 likes
  • 4 in conversation