topic PROC COMPARE - How to output unequal variable names not values in SAS Programming

PROC COMPARE - How to output unequal variable names not values

NickC1 — Fri, 19 Nov 2021 15:56:33 GMT

Hi Folks,

In PROC COMPARE is there any way to output (to a table) only the names of the variables where a value mismatch has been found between the two datasets?

So, not the variable values, just the name of the variable.

I have tried using OUT=table OUTNOEQUAL OUTDIF OUTBASE OUTCOMP but this outputs all the variables which have been compared, and I only want to see the names of variables which have a value mismatch.

For example, I am comparing two datasets with over 2500 variables, say 20 of these variables have unequal values, I want to extract only these variable names for use in a macro which I will create separate tables with obs pulled from both datasets to show the value differences.

I know I could use PRINTALL to display this in the log but I do not have enough programming experience to know how to interrogate the log to extract this information.

Is this possible? Or if I used all the OUT options above could anyone help me with the code to play find the "X" to extract the variable NAME where _TYPE_ = "DIF" from the output dataset?

I hope I have managed to make this question clear. Many thanks for taking the time to read it!

Re: PROC COMPARE - How to output unequal variable names not values

sbxkoenk — Fri, 19 Nov 2021 16:13:47 GMT

I have read your post quickly and diagonally, but this may be what you want :

data work.a;
 set sashelp.class;
 if _N_=10 then height=27000;
run;

ODS TRACE OFF;
ODS OUTPUT CompareSummary=work.CompareSummary;
proc compare base=sashelp.class compare=work.a /* printall */;
   title 'Comparing Two Data Sets: Full Report';
run;

/* then look in the data set work.CompareSummary */

/* end of program */

Koen

Re: PROC COMPARE - How to output unequal variable names not values

data_null__ — Fri, 19 Nov 2021 16:33:47 GMT

Something like this perhaps.

data class;
   set sashelp.class;
   if ranuni(3) lt .5 then do;
      weight = weight+1;
      height = height-1;
      sex    = lowcase(sex);
      end;

   run;
ods select none;
ods trace on;
proc compare novalues base=sashelp.class compare=class;
   ods output CompareSummary=CompareSummary;
   run;
ods trace off;
ods select all;
proc print;
   run;

data vars;
   set CompareSummary;
   retain keep 0;
   if type eq 'h' and strip(batch) eq 'Variables with Unequal Values' then keep=1;
   if keep;
   if type eq 'd' and not missing(batch);
   length var $32;
   var = scan(batch,1,' ');
   run;
proc print;
   run;

Re: PROC COMPARE - How to output unequal variable names not values

NickC1 — Fri, 19 Nov 2021 16:46:52 GMT

Brilliant, thank you! That is exactly what I need, very much appreciate the quick responses.

Re: PROC COMPARE - How to output unequal variable names not values

data_null__ — Fri, 19 Nov 2021 17:17:18 GMT

This is another way that may be useful.

data class;
   set sashelp.class;
   if ranuni(3) lt .5 then do;
      weight = weight+1;
      height = height-1;
      sex    = lowcase(sex);
      end;
    run;
filename lst temp;
ods listing file=lst;
proc compare novalues brief base=sashelp.class compare=class;
   run;
ods listing close;

data vars;
   infile lst col=col;
   input @'NOTE: Values of the following' varnum @;
   input @'variables compare unequal:' @;
   do i = 1 to varnum;
      input variable:$32. @;
      output;
      end;
   stop;
   run;
proc print;
   run;