DATA Step, Macro, Functions and more

PROC COMPARE output list of variables not in base dataset

Accepted Solution Solved
Reply
Occasional Contributor nd
Occasional Contributor
Posts: 8
Accepted Solution

PROC COMPARE output list of variables not in base dataset

Using PROC COMPARE, is there a way to output the list of variables found in Dataset A but not in Dataset B? I want to extract the list of variables so I can drop them from the comparison dataset.

 

 


Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 24,026

Re: PROC COMPARE output list of variables not in base dataset

Posted in reply to Astounding

In general I agree, however the ODS tables out of PROC COMPARE are pretty bad IME. If they've changed in the last two years that would be good though. 

 

delete_proc_compare.JPG

ods output compareVariables=Find;
proc compare data=class compare=class2 all;
run;

 

 

 

View solution in original post


All Replies
Super User
Posts: 24,026

Re: PROC COMPARE output list of variables not in base dataset

Possibly, but I usually roll my own.

 

*create example data;
data class;
set sashelp.class;
run;

data class2;
set sashelp.class;
drop age sex;
run;

*set macro variables;
%let lib_master=work;
%let master=Class2;
%let lib_sub=work;
%let sub=Class;
%let dlm=" ";


*create a variable list of missing variables;
proc sql noprint;
select name into :drop_list separated &dlm.
from sashelp.vcolumn
where upper(libname)=upper("&lib_sub.") 
and upper(memname) = upper("&sub")
and name not in (select name 
                from sashelp.vcolumn 
                where upper(libname)=upper("&lib_master.") 
                and upper(memname) = upper("&master"));
quit;

*show results;
%put &drop_list;

And I have an example here of a more complex approach that does what I call a variable report.

 

https://gist.github.com/statgeek/3b57ae085d9f7a36a2d95c15f04e72e6

Super User
Posts: 6,935

Re: PROC COMPARE output list of variables not in base dataset

[ Edited ]

Any of the information that is part of a printed report would also be available through ODS.  But you will have to figure out how to capture the right data set via ODS (and what the structure of that data set looks like.)  To begin, add this before running PROC COMPARE:

 

ods trace on;

 

Then run the program that includes both this statement and the PROC COMPARE.

 

This will tell you the names of the various pieces of output as far as ODS is concerned.  Hopefully, one of the names looks like the right one (although its possible you would need to explore more than one to find the right one).  At any rate, take a look at how you capture a piece of ODS output:

 

ods output ods_name_for_output_file = your_name_for_sas_dataset_to_hold_results;

 

Then you can run a PROC PRINT and PROC CONTENTS on the file you have captured to get a feel for what is in it.

 

Even if this sounds like work, I recommend doing it.  It is something that will come in handy in the future.

Solution
4 weeks ago
Super User
Posts: 24,026

Re: PROC COMPARE output list of variables not in base dataset

Posted in reply to Astounding

In general I agree, however the ODS tables out of PROC COMPARE are pretty bad IME. If they've changed in the last two years that would be good though. 

 

delete_proc_compare.JPG

ods output compareVariables=Find;
proc compare data=class compare=class2 all;
run;

 

 

 

Occasional Contributor nd
Occasional Contributor
Posts: 8

Re: PROC COMPARE output list of variables not in base dataset

Thanks! Here's my modified code:

 

ods output compareVariables=Find(where=(type="d"));

proc compare base=a compare=b listvar;

run;

ods output close;

data find;

set find;

vars=scan(batch,1);

if missing(vars) then delete;

run;

proc print data=find;

var vars;

run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 80 views
  • 2 likes
  • 3 in conversation