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
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
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
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
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
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
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
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
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
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
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.
Have you explored basic issues about the data? Within the data set WORK.OUT_INC:
From your results, my guess is that's where you start to look, to find out what is wrong.
Sorry I should have pasted in the dataset &input_list
|
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;
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.
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;
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
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;
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);
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;
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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: