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

PROC COMPARE output list of variables not in base dataset

nd — Thu, 26 Jul 2018 17:27:50 GMT

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.

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

Reeza — Thu, 26 Jul 2018 18:11:38 GMT

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

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

Astounding — Thu, 26 Jul 2018 18:34:02 GMT

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.

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

Reeza — Thu, 26 Jul 2018 18:39:16 GMT

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.

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

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

nd — Thu, 26 Jul 2018 19:26:13 GMT

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;

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

BobMcC___ — Thu, 07 Nov 2019 17:27:12 GMT

Actually it could be more useful to use output datasets from Proc Contents. You can merge on variable names from as many input files as you want and use (in=x) flags to check on variables in common in multiple files (or those missing from a particular file, etc.)

eg,

option validvarname=upcase; **because you want to merge on variable name and want upper/lower/mixed case variants to be the same;

proc contents data=deanna.deanna_merge_19_06_28 noprint out= deanna
(keep= label libname memname name
) nodetails;
run;
data deanna;
set deanna;
rename memname = deanna_ds
libname = deanna_lib;
label memname = "deanna_merge_19_06_28";
run;

.....

*repeat for each dataset of interest;

data POR.POR_de_gin_usnd;
informat name POR_unused POR_used usnd1_unused usd2_unused D_or_G
label ;
merge
usound1 (in=u1)
usound2 (in=u2)
ginna (in=g)
deanna (in=d)
POR2 (in=p)
;
by name;
D_or_G = (g = 1 | d = 1); *** existing analysis files;

usnd1_unused = (u1 = 1 & (d = 0 & g = 0)); **Ultrasound 1st trimester vars not used;
usd2_unused = (u2 = 1 & (d = 0 & g = 0)); **Ultrasound 2nd trimester vars not used;
POR_unused = (p = 1 & D_or_G = 0); *** Preg outcome variables not yet used;
POR_used = (p = 1 & D_or_G = 1) *** Preg outcome variables already used;

;
run;