topic Re: How to modify this macro to make it more flexible? in SAS Programming

How to modify this macro to make it more flexible?

smilingmelbourne — Tue, 03 May 2011 08:51:04 GMT

Hi everyone,

I created a macro that extracts a list of variables from a dataset. It works, but it is not very flexible.

%macro generate_vars(dataset=, library=);
%global varlist;
proc sql feedback noprint;
select name into: varlist separated by ' '
from dictionary.columns
where libname=upcase("&library") & memname=upcase("&dataset")
;
quit;
%mend generate_vars;

The reason why I said is is not flexible is because of the global variable "varlist". Everytime I include this macro to extract a list of variables from some dataset, I always must memorize that the macro has a global variable "varlist" and the variable that contains names of dataset variables is "varlist"! I want to modify the macro so it looks like something below

%macro generate_vars(dataset=, library=, varlistname=);

So, suppose on Line 500 of my entire SAS program I want to call this macro. Then, I just do something like:

%generate_vars(dataset=mutual_funds, library=MF, varlistname=anything)

Then, I can check that I have a list of variables by issuing the %put &anything.

How can I discard the global variable "varlist" inside the macro and replace it with an argument to the macro generate_vars? I've tried some but it doesn't work. E.g. the following doesn't work.

%global myvars;
%let myvars=;
%macro generate_vars(dataset=, library=, varlistname=);
proc sql feedback noprint;
select name into : varlistname separated by ' '
from dictionary.columns
where library=upcase("&library") & dataset=upcase("&dataset")
;
quit;

%generate_vars(dataset=A, library=B, varlistname=myvars)
%put &myvars; Message was edited by: smilingmelbourne

Re: How to modify this macro to make it more flexible?

Ksharp — Tue, 03 May 2011 09:34:42 GMT

Maybe like this:
[pre]
select name into : &varlistname separated by ' '
[/pre]
Ksharp

Re: How to modify this macro to make it more flexible?

smilingmelbourne — Tue, 03 May 2011 12:04:29 GMT

Actually I'd tried that several times but it keeps giving errors, something like the variable "varlistname" is not created within the macro.

Re: How to modify this macro to make it more flexible?

Oleg_L — Tue, 03 May 2011 13:55:55 GMT

Hello.

Your code works on SAS 9.1.3.
I've corrected where clause:

%global myvars;
%let myvars=;
%macro generate_vars(dataset=, library=, varlistname=);
proc sql feedback noprint;
select name into : &varlistname separated by ' '
from dictionary.columns
where libname=upcase("&library") & memname=upcase("&dataset")
;
quit;
%mend;

options mprint=1;

%generate_vars(dataset=ie, library=mbm, varlistname=myvars);

%put &myvars;

Re: How to modify this macro to make it more flexible?

art297 — Tue, 03 May 2011 15:33:33 GMT

I'd go one step further and move the %global and %let statements down into the macro. E.g.,
[pre]
%macro generate_vars(dataset=, library=, varlistname=);
%global &varlistname.;
%let &varlistname.=;
proc sql feedback noprint;
select name into : &varlistname separated by ' '
from dictionary.columns
where libname=upcase("&library") and
memname=upcase("&dataset")
;
quit;
%mend;

%generate_vars(dataset=class, library=sashelp, varlistname=myvars);

%put &myvars;
[/pre]

HTH
Art
--------
> Hello.
>
> Your code works on SAS 9.1.3.
> I've corrected where clause:
>
> %global myvars;
> %let myvars=;
> %macro generate_vars(dataset=, library=,
> varlistname=);
> proc sql feedback noprint;
> select name into : &varlistname separated by ' '
> from dictionary.columns
> where libname=upcase("&library") &
> memname=upcase("&dataset")
> ;
> quit;
> %mend;
>
> options mprint=1;
>
> %generate_vars(dataset=ie, library=mbm,
> varlistname=myvars);
>
> %put &myvars;

Re: How to modify this macro to make it more flexible?

chang_y_chung_hotmail_com — Tue, 03 May 2011 21:03:54 GMT

%macro vars(data=, dlm=%str( ));
   %local lib mem ds where keep first name;

   %*-- data should exists --*;
   %let data = %upcase(&data);
   %if not %sysfunc(exist(&data)) %then %do;
      %put ERROR:(vars) data=&data does not exists;
      %return;
   %end;

   %*-- extract libname and memname and prepare ds options --*;
   %let lib = %scan(&data,1,.);
   %let mem = %scan(&data,2,.);
   %if &mem = %then %do;
      %let mem = &lib;
      %let lib = WORK;
   %end;
   %let where = (libname="&lib" and memname="&mem");
   %let keep = libname memname name;

   %*-- extract var names from sashelp.vcolumn view --*;
   %let ds = %sysfunc(open(sashelp.vcolumn(keep=&keep where=&where),is));
   %if &ds=0 %then %do;
      %put ERROR:(vars) vcolumn of &data cannot be opened;
      %return;
   %end;
   %syscall set(ds);
   %let first = 1;
   %do %while(%sysfunc(fetch(&ds))=0);
      %if &first %then %let first = 0;
      %else %*;&dlm;
      %*;%trim(&name)
   %end;
   %let ds = %sysfunc(close(&ds));
%mend vars;

%*-- check --*;
%put ***%vars(data=sashelp.class)***;
%put ***%vars(data=sashelp.class, dlm=%str(, ))***;
%*-- on log
**Name Sex Age Height Weight**
**Name, Sex, Age, Height, Weight**
--*;

Re: How to modify this macro to make it more flexible?

smilingmelbourne — Thu, 05 May 2011 01:37:11 GMT

Thank you very much for helping

Re: How to modify this macro to make it more flexible?

smilingmelbourne — Wed, 11 May 2011 02:29:15 GMT

Hi chang_y_chung@hotmail.com, how can I post/insert the SAS code like you did so it is easier to read?

Re: How to modify this macro to make it more flexible?

sbb — Wed, 11 May 2011 15:11:02 GMT

Review this prior post for details (consider bookmarking as a FAVORITE too):

http://support.sas.com/forums/thread.jspa?messageID=27609

Scott Barry
SBBWorks, Inc.