topic Re: Creating a macro list in PROC SQL using results from EXCEPT in SAS Programming

Creating a macro list in PROC SQL using results from EXCEPT

nd — Fri, 05 Apr 2019 16:47:28 GMT

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!

Re: Creating a macro list in PROC SQL using results from EXCEPT

novinosrin — Fri, 05 Apr 2019 16:58:38 GMT

You have missed EXCEPT ALL

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

Re: Creating a macro list in PROC SQL using results from EXCEPT

Tom — Fri, 05 Apr 2019 16:59:23 GMT

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
)

Re: Creating a macro list in PROC SQL using results from EXCEPT

novinosrin — Fri, 05 Apr 2019 17:01:18 GMT

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 *

Re: Creating a macro list in PROC SQL using results from EXCEPT

Tom — Fri, 05 Apr 2019 17:03:36 GMT

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

Re: Creating a macro list in PROC SQL using results from EXCEPT

ErikLund_Jensen — Fri, 05 Apr 2019 17:03:49 GMT

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)

Re: Creating a macro list in PROC SQL using results from EXCEPT

nd — Fri, 05 Apr 2019 17:09:25 GMT

Thanks, this is a big help!