DATA Step, Macro, Functions and more

How to modify this macro to make it more flexible?

Reply
Contributor
Posts: 73

How to modify this macro to make it more flexible?

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
Super User
Posts: 10,044

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

Posted in reply to smilingmelbourne
Maybe like this:
[pre]
select name into : &varlistname separated by ' '
[/pre]
Ksharp
Contributor
Posts: 73

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

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

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

Posted in reply to smilingmelbourne
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;
PROC Star
Posts: 7,492

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

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;
Regular Contributor
Posts: 241

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

Posted in reply to smilingmelbourne

%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 ERRORSmiley Sadvars) 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 ERRORSmiley Sadvars) 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**
--*;

Contributor
Posts: 73

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

Posted in reply to chang_y_chung_hotmail_com
Thank you very much for helping
Contributor
Posts: 73

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

Posted in reply to chang_y_chung_hotmail_com
Hi chang_y_chung@hotmail.com, how can I post/insert the SAS code like you did so it is easier to read?
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to smilingmelbourne
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.
Ask a Question
Discussion stats
  • 8 replies
  • 263 views
  • 0 likes
  • 6 in conversation