How to modify this macro to make it more flexible?

[smilingmelbourne]

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

[Ksharp]

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

[smilingmelbourne]

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

[Oleg_L]

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;

[art297]

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;

[chang_y_chung_hotmail_com]

%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**
--*;

[smilingmelbourne]

Thank you very much for helping

[smilingmelbourne]

Hi [email protected], how can I post/insert the SAS code like you did so it is easier to read?

[sbb]

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.