I want to create macro list of variables that contains variables are in the NEW dataset only. NEW and OLD only contain the variable "name," generated from proc contents output.
proc sql ; select * into :varlist separated " " from new except select * from old; quit;
If I omit "into :varlist separated " " " I get the list of unique variables in NEW. Using SAS 9.4. Thanks all!
A fun test:
data new;
input a;
cards;
1
2
3
3
4
4
5
;
data old;
input a;
cards;
1
2
5
;
proc sql ;
select * into :varlist separated " "
from
(select *
from new
except all
select * from old);
quit;
%put &varlist;
Results:
%put &varlist;
3 3 4 4
Either way, Please be advised not to use *
You have missed EXCEPT ALL
proc sql ;
select * into :varlist separated " "
from new
except all
select * from old;
quit;
A fun test:
data new;
input a;
cards;
1
2
3
3
4
4
5
;
data old;
input a;
cards;
1
2
5
;
proc sql ;
select * into :varlist separated " "
from
(select *
from new
except all
select * from old);
quit;
%put &varlist;
Results:
%put &varlist;
3 3 4 4
Either way, Please be advised not to use *
That works because * expands to just a single variable since the result of the inner query only has one column.
You cannot select * into a macro variable. You can only select a single expression.
You can only use INTO on the outer most query.
select name into :namelist separated by ' '
from
(select name from new
except
select name from old
)
Hi @nd
It is not clear what you want. Is it a list of variable names or variable values?, and how should they be separated?
Please provide example data for new and old data sets and the expected output (macro variable value)
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 25. 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.