BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
brainupgraded
Obsidian | Level 7

 

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!


 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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 ;

 

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
brainupgraded
Obsidian | Level 7

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
Calcite | Level 5
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
Obsidian | Level 7
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
Diamond | Level 26

@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.

Insert Log Icon in SAS Communities.png

--
Paige Miller
Tom
Super User Tom
Super User

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
Obsidian | Level 7
Works perfectly. Thank you so much!
Avihaa
Calcite | Level 5

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
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1358 views
  • 8 likes
  • 5 in conversation