Creating a macro list in PROC SQL using results from EXCEPT

[nd]

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! 

[novinosrin]

You have missed EXCEPT ALL

proc sql ;
select * into :varlist separated " " 
from  new
except  all
select * from old;
quit;

[novinosrin · Accepted solution]

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 * 

[Tom]

That works because * expands to just a single variable since the result of the inner query only has one column.

[nd]

Thanks, this is a big help!

[Tom]

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
)

[ErikLund_Jensen]

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)