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

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

5 REPLIES 5
LinusH
Tourmaline | Level 20

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
data_null__
Jade | Level 19

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__
Jade | Level 19

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
Super User Tom
Super User

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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