Re: SAS: Warning Apparent symbolic reference not resolved

dxs8292 · Posted 05-10-2019 03:46 PM

I am running the macro variable %extract from a bigger program, but I get a warning that certain macro variables are not resolve. These macro variables get created in the %extract macro(using proc sql). The macro variables are numlist1--numlist6, these macros variables called later in a datastep to drop a bunch of variables.

The SAS Macro is:

%macro extract(input_data,input_list,size,output_data);

proc sql noprint;

select distinct trim(variable)

into :numlist1 separated by ' '

from &input_list

where counter <= 1000;

quit;

%do j=2 %to &size;

proc sql noprint;

select distinct trim(variable)

into: numlist&j separated by ' '

from &input_list

where %eval((&j-1)*1000) < counter <= %eval(&j*1000);

quit;

%end;

data &output_data(compress=yes);set &input_data;

drop %do j=1 %to &size; &&numlist&j %end;;

run;

%mend;

%extract(&Prefix.OUT_INC,&SRC73,&numobs73,&OUTPUT_INC_FEAT);

The following is the SAS Log

MLOGIC(EXTRACT): Beginning execution.

SYMBOLGEN: Macro variable PREFIX resolves to WORK.

SYMBOLGEN: Macro variable SRC73 resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable NUMOBS73 resolves to 6

SYMBOLGEN: Macro variable OUTPUT_INC_FEAT resolves to DATAMODL.COPD_COMM_INC_PROD

74492 +%extract(&Prefix.OUT_INC,&SRC73,&numobs73,&OUTPUT_INC_FEAT);

MLOGIC(EXTRACT): Parameter INPUT_DATA has value WORK.OUT_INC

MLOGIC(EXTRACT): Parameter INPUT_LIST has value DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

MLOGIC(EXTRACT): Parameter SIZE has value 6

MLOGIC(EXTRACT): Parameter OUTPUT_DATA has value DATAMODL.COPD_COMM_INC_PROD

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

MPRINT(EXTRACT): select distinct trim(variable) into :numlist1 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

counter <= 1000;

MPRINT(EXTRACT): quit;

SYMBOLGEN: Macro variable SIZE resolves to 6

MLOGIC(EXTRACT): %DO loop beginning; index variable J; start value is 2; stop value is 6; by value is 1.

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable J resolves to 2

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 2

MPRINT(EXTRACT): select distinct trim(variable) into: numlist2 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

1000 < counter <= 2000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 3; loop will iterate again.

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable J resolves to 3

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 3

MPRINT(EXTRACT): select distinct trim(variable) into: numlist3 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

2000 < counter <= 3000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 4; loop will iterate again.

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable J resolves to 4

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 4

MPRINT(EXTRACT): select distinct trim(variable) into: numlist4 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

3000 < counter <= 4000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 5; loop will iterate again.

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable J resolves to 5

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 5

MPRINT(EXTRACT): select distinct trim(variable) into: numlist5 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

4000 < counter <= 5000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 6; loop will iterate again.

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable J resolves to 6

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 6

MPRINT(EXTRACT): select distinct trim(variable) into: numlist6 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

5000 < counter <= 6000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 7; loop will not iterate again.

SYMBOLGEN: Macro variable OUTPUT_DATA resolves to DATAMODL.COPD_COMM_INC_PROD

MPRINT(EXTRACT): data DATAMODL.COPD_COMM_INC_PROD(compress=yes);

SYMBOLGEN: Macro variable INPUT_DATA resolves to WORK.OUT_INC

MPRINT(EXTRACT): set WORK.OUT_INC;

SYMBOLGEN: Macro variable SIZE resolves to 6

MLOGIC(EXTRACT): %DO loop beginning; index variable J; start value is 1; stop value is 6; by value is 1.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 1

WARNING: Apparent symbolic reference NUMLIST1 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 2; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 2

WARNING: Apparent symbolic reference NUMLIST2 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 3; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 3

WARNING: Apparent symbolic reference NUMLIST3 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 4; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 4

WARNING: Apparent symbolic reference NUMLIST4 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 5; loop will iterate again.

SYMBOLGEN: && resolves to &.

5796 The SAS System

SYMBOLGEN: Macro variable J resolves to 5

WARNING: Apparent symbolic reference NUMLIST5 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 6; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 6

WARNING: Apparent symbolic reference NUMLIST6 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 7; loop will not iterate again.

MPRINT(EXTRACT): drop &numlist1 &numlist2 &numlist3 &numlist4 &numlist5 &numlist6;

MPRINT(EXTRACT): run;

MLOGIC(EXTRACT): Ending execution.

MLOGIC(EXTRACT): Beginning execution.

SYMBOLGEN: Macro variable PREFIX resolves to WORK.

SYMBOLGEN: Macro variable SRC73 resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable NUMOBS73 resolves to 6

SYMBOLGEN: Macro variable OUTPUT_INC_FEAT resolves to DATAMODL.COPD_COMM_INC_PROD

74492 +%extract(&Prefix.OUT_INC,&SRC73,&numobs73,&OUTPUT_INC_FEAT);

MLOGIC(EXTRACT): Parameter INPUT_DATA has value WORK.OUT_INC

MLOGIC(EXTRACT): Parameter INPUT_LIST has value DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

MLOGIC(EXTRACT): Parameter SIZE has value 6

MLOGIC(EXTRACT): Parameter OUTPUT_DATA has value DATAMODL.COPD_COMM_INC_PROD

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

MPRINT(EXTRACT): select distinct trim(variable) into :numlist1 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

counter <= 1000;

MPRINT(EXTRACT): quit;

SYMBOLGEN: Macro variable SIZE resolves to 6

MLOGIC(EXTRACT): %DO loop beginning; index variable J; start value is 2; stop value is 6; by value is 1.

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable J resolves to 2

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 2

MPRINT(EXTRACT): select distinct trim(variable) into: numlist2 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

1000 < counter <= 2000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 3; loop will iterate again.

MPRINT(EXTRACT): proc sql noprint;

5795 The SAS System

SYMBOLGEN: Macro variable J resolves to 3

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 3

MPRINT(EXTRACT): select distinct trim(variable) into: numlist3 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

2000 < counter <= 3000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 4; loop will iterate again.

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable J resolves to 4

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 4

MPRINT(EXTRACT): select distinct trim(variable) into: numlist4 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

3000 < counter <= 4000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 5; loop will iterate again.

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable J resolves to 5

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 5

MPRINT(EXTRACT): select distinct trim(variable) into: numlist5 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

4000 < counter <= 5000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 6; loop will iterate again.

MPRINT(EXTRACT): proc sql noprint;

SYMBOLGEN: Macro variable J resolves to 6

SYMBOLGEN: Macro variable INPUT_LIST resolves to DATAMODL.PROD_COPD_COMM_INC_DROPFEAT

SYMBOLGEN: Macro variable J resolves to 6

MPRINT(EXTRACT): select distinct trim(variable) into: numlist6 separated by ' ' from DATAMODL.PROD_COPD_COMM_INC_DROPFEAT where

5000 < counter <= 6000;

MPRINT(EXTRACT): quit;

MLOGIC(EXTRACT): %DO loop index variable J is now 7; loop will not iterate again.

SYMBOLGEN: Macro variable OUTPUT_DATA resolves to DATAMODL.COPD_COMM_INC_PROD

MPRINT(EXTRACT): data DATAMODL.COPD_COMM_INC_PROD(compress=yes);

SYMBOLGEN: Macro variable INPUT_DATA resolves to WORK.OUT_INC

MPRINT(EXTRACT): set WORK.OUT_INC;

SYMBOLGEN: Macro variable SIZE resolves to 6

MLOGIC(EXTRACT): %DO loop beginning; index variable J; start value is 1; stop value is 6; by value is 1.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 1

WARNING: Apparent symbolic reference NUMLIST1 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 2; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 2

WARNING: Apparent symbolic reference NUMLIST2 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 3; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 3

WARNING: Apparent symbolic reference NUMLIST3 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 4; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 4

WARNING: Apparent symbolic reference NUMLIST4 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 5; loop will iterate again.

SYMBOLGEN: && resolves to &.

5796 The SAS System

SYMBOLGEN: Macro variable J resolves to 5

WARNING: Apparent symbolic reference NUMLIST5 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 6; loop will iterate again.

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable J resolves to 6

WARNING: Apparent symbolic reference NUMLIST6 not resolved.

MLOGIC(EXTRACT): %DO loop index variable J is now 7; loop will not iterate again.

MPRINT(EXTRACT): drop &numlist1 &numlist2 &numlist3 &numlist4 &numlist5 &numlist6;

MPRINT(EXTRACT): run;

MLOGIC(EXTRACT): Ending execution.

Astounding · Posted 05-10-2019 05:40 PM

Have you explored basic issues about the data? Within the data set WORK.OUT_INC:

Does it contain any observations at all?
Does it contain a variable named COUNTER with values that are less than 1,000 ?
Does it contain a variable named VARIABLE with non-missing values in it?

From your results, my guess is that's where you start to look, to find out what is wrong.

dxs8292 · Posted 05-11-2019 07:11 PM

Sorry I should have pasted in the dataset &input_list

The CONTENTS Procedure

Data Set Name	DATAMODL.PROD_COPD_COMM_INC_DROPFEAT	Observations	5568
Member Type	DATA	Variables	2
Engine	BASE	Indexes	0
Created	04/26/2019 20:00:02	Observation Length	40
Last Modified	04/26/2019 20:00:02	Deleted Observations	0
Protection		Compressed	CHAR
Data Set Type		Reuse Space	NO
Label		Point to Observations	YES
Data Representation	HP_UX_64, RS_6000_AIX_64, SOLARIS_64, HP_IA64	Sorted	NO
Encoding	latin1 Western (ISO)

Engine/Host Dependent Information

Data Set Page Size	262144
Number of Data Set Pages	3
Number of Data Set Repairs	0
Filename	/datamodl/prod_copd_comm_inc_dropfeat.sas7bdat
Release Created	9.0401M3
Host Created	AIX
Inode Number	1077755
Access Permission	rw-r-----
Owner Name	jxf9025
File Size	1MB
File Size (bytes)	1048576

Alphabetic List of Variables and Attributes# Variable Type Len

2	counter	Num	8
1	variable	Char	32

Tom · Posted 05-12-2019 11:53 AM

I never like using "arrays" of macro variables. Usually they are used in situations like this one where they are not needed.

Just generate the DROP statement into a file and then %INCLUDE the file.

%macro extract(input_data,input_list,size,output_data);

filename droplist temp;
data _null_;
  set &input_list end=eof;
  where counter <= &size * 1000;
  file droplist lrecl=80 ;
  if _n_=1 then put 'drop ' @;
  put variable @;
  if eof then put ';' ;
run;

data &output_data(compress=yes);
  set &input_data;
%include droplist ;
run;

%mend;

Patrick · Posted 05-11-2019 01:37 AM

@dxs8292

A PROC SQL INTO clause only creates macro variables if the SQL returns rows. It appears that doesn't happen in your case and for that reason the macro variables don't get created at all (and though the warnings).

If you want to ensure that the macro variables always exist then add a piece of code which does this for you. Something like:

  %do j=1 %to &size;
    %let numlist&j=;
  %end;

Eventually consider to also add some logic to check if your SQL returns the number of rows you'd expect. The automatic macro variable &SQLOBS is most useful for that.

ErikLund_Jensen · Posted 05-11-2019 05:02 AM

Hi @dxs8292

I wonder why you need all the looping. If I understand your code, you create several lists of variables, where each list contains variables with counter value in a given 1000-interval, and then you drop all these variables from the output data set.

As pointed out by others, there are several things that can give problems, and with a simpler code fewer things can go wrong. So - unless I missed something - this code will do the same:

%macro extract(input_data, input_list, size, output_data);
	%let droplist =;
	proc sql noprint;
		select distinct trim(variable)
		into :numlist separated by ' '
		from &input_list
		where counter <= %eval(&size*1000);
	quit;
	%if &sqlobs > 0 %then %let droplist = drop &numlist;

	data &output_data(compress=yes); 
		set &input_data;
		&droplist;
	run;
%mend;

dxs8292 · Posted 05-11-2019 07:01 PM

I need to do looping because my macro variable gets very large and get the following message if I don't do any looping

ERROR: The length of the value of the macro variable NUMLIST (65540) exceeds the maximum length (65534). The value has been

truncated to 65534 characters.

This is huge dataset with over 20,000 variables and I want to drop over 5000 variables, In other datasets I even want to drop more variables. That is why I am doing all the looping

Patrick · Posted 05-11-2019 11:20 PM

@dxs8292

Below coding approach should be able to deal with your variable galore without the need of looping.

/* create sample data */
data have;
  array vv_123456789_123456789_abc_ {20000} $1 (20000*'A');
  set sashelp.class;
run;

data vars_to_drop(drop=_:);
  length variable $32;
  do _i=1 to 9999, 10001 to 20000;
    variable=cats('vv_123456789_123456789_abc_',put(_i,f5.));
    output;
  end;
  stop;
run;

/* define and execute macro to drop variables from source dataset */
%macro DropVars(input_data, drop_list, output_data);
  %if %bquote(&output_data) = %bquote() %then %let output_data=input_data;

  /* generate data step code for dropping variables */
  filename codegen temp;
  data _null_;
  /*  file print;*/
    file codegen;
    set &drop_list end=last;
    if _n_=1 then
      do;
        put 
            "data &output_data;"
          / "  set &input_data (drop="
          ;
      end;
    put "    " variable;
    if last then
      do;
        put 
            "    );"
          / "run;"
          ;
      end;
  run;

  /* execute generated code */
  %include codegen / nosource2;
/*  %include codegen / source2;*/

  filename codegen clear;
%mend;

%DropVars(have, vars_to_drop, want);

proc contents data=want;;
run;

Also: If you've got that many variables then I'd assume there is some naming pattern used. Have you already investigated if you could eventually take advantage of this to use any of below syntax.


/* dash: drop ranges of variables - requires the variables to end with a number */
data want2;
  set have(drop=
            vv_123456789_123456789_abc_1 - vv_123456789_123456789_abc_9999 
            vv_123456789_123456789_abc_10001 - vv_123456789_123456789_abc_20000
            );
run;
proc contents data=want2;
run;

/* column: drop variables using column modifier (wildcard) */
data want3;
  set have(drop=vv_123456789_123456789_abc_:);
run;
proc contents data=want3;
run;

/* double dash: drop ranges of variables based on physical sequence in data set (varnum)*/
data have4;
  length varY varC varX varB varZ $1;
  stop;
run;
proc contents data=have4 varnum;
run;
data want4(drop=varC -- varB);
  set have4;
run;
proc contents data=want4;
run;

ErikLund_Jensen · Posted 05-12-2019 05:19 AM

Hi @dxs8292

Sorry - I didn't think of that, because I never had to deal with tables that wide. But then I think a better solution would be to avoid macro vaiables instead of trying to circumvent the 65k limit.

I tried the following code and found it a working solution with 50000 variables. The log becomes rather big, because each call execute line is written to it, but that part of the log could be redirected.

%macro extract(input_data, input_list, size, output_data);
	proc sql;
		create table droplist as
			select variable 
			from &input_list
			where counter < 0;* %eval(&size*1000);
	quit;

	data _null_; set droplist end=eof;
		if _N_ = 1 then call execute("data &output_data; set &input_data;");
		call execute("drop "||variable||";");
		if eof then call execute("run;");
	run;
%mend;
%extract(input_data, input_list, 6, output_data);

ErikLund_Jensen · Posted 05-12-2019 05:26 AM

Hi @dxs8292

I forgot to post my test data, here they are for your further experiments:

* make a test data set with 50000 variables;
data x; 
	do i = 1 to 50000;
		var = i;
		output;
	end;
run;
proc transpose data=x out=input_data prefix=Variable_name_;
run;

* make a test variable list with counter = 0, 1000, 2000 ... 9000;
data input_list; 
	do i = 1 to 50000;
		variable = 'Variable_name_'||strip(put(i,8.));
		counter = mod(i,10)*1000;
		output;
	end;
run;

Patrick · Posted 05-13-2019 12:25 AM

@ErikLund_Jensen

I've never considered using a single CALL EXECUTE() for anything else than a full run-group. Learned something new 🙂

Using what you've posted with my sample data - and it works quite well.

/* create sample data */
data have;
  array vv_123456789_123456789_abc_ {20000} $1 (20000*'A');
  set sashelp.class;
run;

data vars_to_drop(drop=_:);
  length variable $32;
  do _i=1 to 9999, 10001 to 20000;
    variable=cats('vv_123456789_123456789_abc_',put(_i,f5.));
    output;
  end;
  stop;
run;

%macro extract(input_data, input_list, output_data);
  data _null_;
    set &input_list end=eof;
    if _N_ = 1 then
      call execute("data &output_data; set &input_data(drop=");
    call execute(variable);
    if eof then
      call execute("); run;");
  run;
%mend;
%extract(have, vars_to_drop, want);

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away