Proc SQL within Macro to get the list of variable names

[brainupgraded]

Hello, 

I am trying to get the list of variables from each of the four data sets, so I want to use macro for the task. I can successfully get the lists if I don't use macro (see below codes for one data set). 

proc sql noprint;
	select name into : vars_num_s separated by " "
	from dictionary.columns
	where LIBNAME=upcase("work")
	and MEMNAME=upcase("nexus_s")
	/*and name not like '%_complete' /* excluding any variable with "_complete' suffix */
	and upcase(name) ne upcase("a_subject_id") /* excluding the id variable */
	and type="num" /* selecting numeric variables only */
	order by name;
quit;
proc sql noprint;
	select name into : vars_cha_s separated by " "
	from dictionary.columns
	where LIBNAME=upcase("work")
	and MEMNAME=upcase("nexus_s")
	/*and name not like '%_complete'*/
	and upcase(name) ne upcase("a_subject_id")
	and type ne "num" /* selecting character variables only */
	order by name;
quit;

After that, I can successfully use local macros with variable lists in data step. 

data nexus_retain;set nexus_wide;
	array num_s {*} &vars_num_s;
	array num_rs1 {*} &vars_num_rs1;
	array num_rs2 {*} &vars_num_rs2;
	array num_b {*} &vars_num_b;

	array cha_s {*} $ &vars_cha_s;
	array cha_rs1 {*} $ &vars_cha_rs1;
	array cha_rs2 {*} $ &vars_cha_rs2;
	array cha_b {*} $ &vars_cha_b;

	do i=1 to dim(num_s);
			if num_rs1[i]^=. then num_s[i]=num_rs1[i];
			if num_rs2[i]^=. then num_s[i]=num_rs2[i];
			if num_b[i]^=. then num_s[i]=num_b[i];
	end;
	do i=1 to dim(cha_s);
			if cha_rs1[i]^="" then cha_s[i]=cha_rs1[i];
			if cha_rs2[i]^="" then cha_s[i]=cha_rs2[i];
			if cha_b[i]^="" then cha_s[i]=cha_b[i];
	end;

	drop i;
	drop &num_rs1 &num_rs2 &num_b;
	drop &cha_rs1 &cha_rs2 &cha_b; 
run;

However, below codes for macro "varlist" do not work. 

%macro varlist(lib, dsn, varlist_num, varlist_cha);
	proc sql noprint;
		select name into : &varlist_num. separated by " "
		from dictionary.columns
		where LIBNAME=upcase("&lib.")
		and MEMNAME=upcase("&dsn.")
		/*and name not like '%_complete' /* excluding any variable with "_complete' suffix */
		and upcase(name) ne upcase("a_subject_id") /* excluding the id variable */
		and type="num" /* selecting numeric variables only */
		order by name;
	quit;
	proc sql noprint;
		select name into : &varlist_cha. separated by " "
		from dictionary.columns
		where LIBNAME=upcase("&lib.")
		and MEMNAME=upcase("&dsn.")
		/*and name not like '%_complete'*/
		and upcase(name) ne upcase("a_subject_id")
		and type ne "num" /* selecting character variables only */
		order by name;
	quit;
%mend varlist;

I don't get any error messages, but when I run the data step above, I get multiple error messages, such as "WARNING: Apparent symbolic reference VARS_NUM_S not resolved". I think it is related to how I use the local macros, including vars_num_s, ... vars_cha_b, but I am not sure. 

Please advise!

[PaigeMiller]

Do you actually call %VARLIST? You don't show us that.

[brainupgraded]

Yes, sorry, I thought that part was not necessary. But any of these do not give me any error messages. 

%varlist(work, nexus_s, vars_num_s, vars_cha_s);
%varlist(work, nexus_rs1, vars_num_rs1, vars_cha_rs1);
%varlist(work, nexus_rs2, vars_num_rs2, vars_cha_rs2);
%varlist(work, nexus_b, vars_num_b, vars_cha_b);

[Avihaa]

Use the _NUMERIC_, _CHARACTER_, and _ALL_ keywords to specify variables of a certain type (numeric or character) or all types.
Use a single hyphen (-) to specify a range of variables that have a common prefix and a sequential set of numerical suffixes.

[brainupgraded]

I really wish I could. But I need to exclude some variables from the list for each data set so I used Proc SQL. It works, and what I am trying to do is to make the macro I wrote work.

[PaigeMiller]

---

@brainupgraded wrote:

Yes, sorry, I thought that part was not necessary. But any of these do not give me any error messages. 

%varlist(work, nexus_s, vars_num_s, vars_cha_s);
%varlist(work, nexus_rs1, vars_num_rs1, vars_cha_rs1);
%varlist(work, nexus_rs2, vars_num_rs2, vars_cha_rs2);
%varlist(work, nexus_b, vars_num_b, vars_cha_b);

---

You would be surprised how often people don't actually call the macro.

Please turn on macro debugging tools by running this command.

options mprint symbolgen;

Then, run your code again and show us the ENTIRE log for one run of %VARLIST. Copy the log as text and paste it into the window that appears when you click on the </> icon.

[Tom · Accepted solution]

If you run those SELECT statement inside a macro and the target macro variables do not already exist in some higher level scope (either the GLOBAL scope or the scope of some other macro that called your macro) then their scope will be local to the macro that ran the SELECT statement.  So they will disappear when the macro ends.

Since you appear to just be making only TWO macro variables perhaps you could just make the macro smart enough to force the macro variables into the GLOBAL symbol table when they do not already exist.

You should also use the NLITERAL() function to protect yourself from users setting the VALIDVARNAME option to ANY and creating goofy variable names.

%macro varlist(lib, dsn, varlist_num, varlist_cha);
%if not %symexist(&varlist_num) %then %global &varlist_num;
%if not %symexist(&varlist_cha) %then %global &varlist_cha;
proc sql noprint;
  select case when (type='num') then nliteral(name) else ' ' end
       , case when (type='char') then nliteral(name) else ' ' end
    into :&varlist_num. separated by " "
       , :&varlist_cha. separated by " "
    from dictionary.columns
    where LIBNAME=upcase("&lib.")
      and MEMNAME=upcase("&dsn.")
/* excluding any variable with _complete suffix */
/*    and lowcase(name) not like '%^_complete' escape '^' */ 
/* excluding the id variable */
      and upcase(name) ne upcase("a_subject_id")
    order by name
  ;
quit;
%mend varlist;
%mend ;

[brainupgraded]

Works perfectly. Thank you so much!

[Avihaa]

Use the _NUMERIC_, _CHARACTER_, and _ALL_ keywords to specify variables of a certain type (numeric or character) or all types.
Use a single hyphen (-) to specify a range of variables that have a common prefix and a sequential set of numerical suffixes.

The February 2022 European Masters was a professional ranking snooker tournament that took place from 21 to 27 February 2022 at the Marshall Arena in Milton Keynes, England. The tournament was the 11th ranking event of the 2021–22 season and the sixth of eight tournaments in the season's European Series.






<a href="https://anonigstalk.com/">anonigviewer</a>
<a href="https://bingenerator.one/">bingenerator</a>

[ballardw]

Scope of macro variables. Your created macro variables only exist for the duration of the macro currently.

See this example:

%macro dummy(list);
   %let &list= This is some text assigned to macro variable;
%mend;
%macro dummy2(list);
   %global &list;
   %let &list= This is some text assigned to macro variable;
%mend;

%dummy(mylist);
%put &mylist;

%dummy2(mylist2);
%put &mylist2;

The variable created in Dummy is local to the macro, in Dummy2 it is defined to exist in the Global table so has values after and outside when the macro executes