Here's a Helpful Macro to Generate List of Unknown Variables with Known Suffix

[dgritt]

* Use like 'Prefix:' for variable lists, but this will generate a list of variables with a suffix *;

options validvarname=upcase;

%global suffix varct vars;

%macro suffix(dsn,suffix);

 %put dsn=&dsn. suffix=&suffix.;

 %if %index(&dsn,.)
 %then %do;
 %let lib = %trim(%upcase(%scan(&dsn.,1,.)));
 %let dsn = %trim(%upcase(%scan(&dsn.,2,.)));
 %end;
 %else %do;
 %let lib = WORK;
 %let dsn = %trim(%upcase(&dsn.));
 %end;

 %let suffix = %trim(%upcase(&suffix.));

 proc sql noprint;
 select strip(lowcase(name)) as name
 into :vars separated by ' '
 from sashelp.vcolumn
 where libname = "&lib."
 and memname = "&dsn."
 and upcase(name) like "%nrstr(%%)&suffix.";

 %let varct = &sqlobs.;
 quit;

 %put lib=&lib. dsn=&dsn. suffix=&suffix. varct=&varct. vars=&vars.;
%mend suffix;

%suffix(sashelp.baseball,Bat);

lib=SASHELP dsn=BASEBALL suffix=BAT varct=2 vars=natbat cratbat

[ballardw]

Since there is no question I am assuming that this is posted as a helpful macro, correct?

Note that if you have a moderate to large number of libraries and data sets that

proc sql noprint;
  create table vars as
  select strip(lowcase(name)) as name
    from Dictionary.columns
   where libname = "&lib."

may run noticeably faster than using sashelp.vcolumn. At least that has been my experience with my install.

I think the title should be more "find variables in a dataset with a specific suffix".

Also you don't need to create table and then reread it:

proc sql noprint;

  select strip(lowcase(name)) into : vars separated by ' '
    from Dictionary.columns
   where libname = "&lib."
     and memname = "&dsn."
     and name like "%nrstr(%%)&suffix.";
quit;

should work.

[dgritt]

Correct, this is just a helpful macro.

Thanks for the suggestions @ballardw, I have found sashelp to be slow. The create table is to obtain the count - do you have a more efficient method for doing that?

[data_null__]

https://www.lexjansen.com/pharmasug/2016/BB/PharmaSUG-2016-BB05.pdf

[ballardw]

---

@dgritt wrote:

Correct, this is just a helpful macro.

Thanks for the suggestions @ballardw, I have found sashelp to be slow. The create table is to obtain the count - do you have a more efficient method for doing that?

---

The proc sql creates an automatic macro variable named &sqlobs that contains the number of records returned in the most recent query. See this example:

proc sql;
   select name into: namelist separated by ' '
   from sashelp.class
   where sex='F';
quit;
%let namecount=&sqlobs;
%put Names are &namelist., namecount is &namecount.;

I recommend immediately assigning sqlobs to another macro variable as it is changed by every SQL call.

Isn't SAS helpful sometimes?

[ChrisNZ]

@ballardw > I think the title should be more "find variables in a dataset with a specific suffix".

I like that the title "Here's a Helpful Macro to Generate List of Unknown Variables with Known Suffix" makes it clear this proposes a solution to a problem, and is not a question.

[dgritt]

That's true. Probably an older macro I should've updated. At the time I must not be have thought that'd work when using into.

[Ksharp]

%let var=sex name age;
%let suffix=_ed ;
%let want=%sysfunc(prxchange(s/(\w+)/$1&suffix/,-1,&var));

%put &want ;

[dgritt]

@Ksharp, that assumes you know all the variables and want to append a suffix. My macro searches a dataset for an unknown variable list based on a common, existing suffix.

[ballardw]

---

@dgritt wrote:
@Ksharp, that assumes you know all the variables and want to append a suffix. My macro searches a dataset for an unknown variable list based on a common, existing suffix.

---

Which is why I suggested changing the title of the post. When I saw the title I expected to see something along the lines that @Ksharp posted, or a question on how to write such.

Also It is a good idea in a macro definition to include a comment inside the macro as to the intended purpose and sometime even an example of a call to the macro.

[dgritt]

@bballard, the title made sense to me, but I changed it. I also updated the contents.

[s_lassen]

I try to avoid returning values in global macro variables when I can. In this case it is not hard. First, here is a macro to get a variable list from a table (with a little helper macro first):

/* Execute a function, check SYSRC() and possibly write SYSMSG() */
%macro chkfunc(func);
  %*;%sysfunc(&func)
  %if %sysfunc(sysrc()) %then
     %put %qsysfunc(sysmsg());
%mend;

/* Create a variable list from KEEP and DROP specifications           */
/* NB: If both are used, the intersection of the two will be returned */
%macro varlist(data,keep=,drop=,separator=%str( ));
  %local ds dskeep dsdrop rc varnum nvars varname sep;
  %let ds=%chkfunc(open(&data));
  %if &ds=0 %then %return;
  %let dskeep=%chkfunc(open(&data(keep=&keep)));
  %if &dskeep=0 %then %goto errorkeep;
  %let dsdrop=%chkfunc(open(&data(drop=&drop)));
  %if &dsdrop=0 %then %goto errordrop;
  %do varnum=1 %to %chkfunc(attrn(&ds,nvars));
    %let varname=%chkfunc(varname(&ds,&varnum));
    /* No %CHKFUNC in the following, we do not want error */
    /* messages when variables not found                  */
    %if %sysfunc(varnum(&dskeep,&varname)) and
        %sysfunc(varnum(&dsdrop,&varname)) %then %do;
      %*;&sep.&varname
      %let sep=&separator;
      %end;
    %end;
  %let rc=%chkfunc(close(&dsdrop));
%errordrop:
  %let rc=%chkfunc(close(&dskeep));
%errorkeep:
  %let rc=%chkfunc(close(&ds));
%mend;

Now you can get a list of variables from a dataset in a simple function-call style macro, e.g.

%let a=%varlist(sashelp.class,drop=_character_);

This can easily be used to get variables starting with some string (use ":" as a wild-card in the keep parameter). To get the names ending with something, here is another macro, which checks a list against a PRX expression, and returns the items matching:

/* Filter list of words using PRXMATCH */
%macro prxfilter(prx,str,delim=%str( ),separator=%str( ),options=);
  %local i w prxid scanq quote scanf sysf sep;
  %let prx=%superq(prx);
  %let options=%upcase(&options);
  /* option Quote means that the output will be quoted */
  %let quote=%sysfunc(indexw(&options,QUOTE));
  %if &Quote %then
    sysf=qsysfunc;
  %else
    %let sysf=sysfunc;
  /* Options SCANQ means that the SCANQ function will be used */
  %let scanq=%sysfunc(indexw(&options,SCANQ));
  %if &scanq %then
    %let scanf=SCANQ;
  %else
    %let scanf=SCAN;
  %if %sysfunc(verify(&prx,1234567890))=0 %then
    %let prxid=&prx; /* The prx parameter is a PRX ID */
  %else
    %let prxid=%sysfunc(prxparse(&prx));
  %if &prxid=0 %then
    %return;
  /* COUNTW is only available from 9.2, hence this "primitive" approach */
  %do i=1 %to 99999;
    %let w=%&sysf(&scanf(&str,&i,&delim));
    %if %length(&w)=0 %then %do;
      %if &prx ne &prxid %then
        /* we allocated a local PRX ID, free it */
        %syscall prxfree(prxid);
      %return;
      %end;
    %if %sysfunc(prxmatch(&prxid,&w)) %then %do;
      %*;&sep.&w
      %let sep=&separator;
      %end;
    %end;
%mend;

So, you can now get your bats like this:

%put %prxfilter(/bat$/i,%varlist(sashelp.baseball));

The nice thing about this approach is that you can use the macro functions whereever you like, e.g.

proc sql;
  select distinct
    %prxfilter(/bat$/i,%varlist(sashelp.baseball),separator=%str(,))
  from sashelp.baseball;

The problem with using global macro variables to return values are two. One is that they sometimes collide, meaning that one macro overwrites the global variables of another. The other is that sometimes you have macros call other macros, and so on. If your macro is really considered useful, there is great chance that it will be embedded in another macro. And then you will call that macro from a third macro. And your new macro will have a local variable called e.g.SUFFIX, and you will get the message "Attempt to globalize a variable already defined as local", and you macro will not run.

This is why, when I have to put something in a macro variable in order to return a value, I often use a "SEMIGLOBAL" construct. It is very simple: If a macro variable is not already defined in the calling environment, define is as global. Otherwise, do nothing. Like this:

%macro semiglobal/parmbuff;
  %let syspbuff=%scan(&syspbuff,1,( ,)); /* only one name will be used */
  %if not %symexist(&syspbuff) %then
    %global &syspbuff;
%mend;

So,l now you can have your macros return values in global or local variables. So if you have a macro that returns something in a macro variable, declare that macro variable Semi-global:

%macro x;
  %semiglobal(rvalue); /* return value */
  /* more stuff here */
%mend;

Another macro calling the X macro can then make the RVALUE variable local without any problems (actually, avoiding possible problems in the future).