BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nd
Obsidian | Level 7 nd
Obsidian | Level 7

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

5 REPLIES 5
Reeza
Super User

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

Astounding
PROC Star

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.

Reeza
Super User

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;

 

 

 

nd
Obsidian | Level 7 nd
Obsidian | Level 7

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;

BobMcC___
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 9036 views
  • 3 likes
  • 4 in conversation