topic Re: Create data set "CompareDifferences" from proc compare. in SAS Procedures

Create data set "CompareDifferences" from proc compare.

Yuriy2301 — Fri, 15 Oct 2010 10:33:16 GMT

Hello,
So some SAS procedures assign names to the tables that they create.
Proc compare also assign such names, few of them:"CompareDifferences","CompareVariables" etc.

I want to make data set which consists of data from "CompareDifferences".

I know that when using ODS, exists possibility to create output data sets by referencing these names, but I can't find any code which show's how to do it...

Suppose it must be simple using of some ODS and proc compare options...

Thanks!

Re: Create data set "CompareDifferences" from proc compare.

Reeza — Fri, 15 Oct 2010 23:56:33 GMT

ODS Tables and ODS TRACE...

data one;
input student year $ state $ gr1 gr2;
label year='Year of Birth';
format gr1 4.1;
datalines;
1000 1970 NC 85 87
1042 1971 MD 92 92
1095 1969 PA 78 72
1187 1970 MA 87 94
;

data two;
input student $ year $ state $ gr1
gr2 major $;
label state='Home State';
format gr1 5.2;
datalines;
1000 1970 NC 84 87 Math
1042 1971 MA 92 92 History
1095 1969 PA 79 73 Physics
1187 1970 MD 87 74 Dance
1204 1971 NC 82 96 French
;

ods trace on;
proc compare base=one
compare=two novalues;
run;

ods table CompareVariables=Check1;
proc compare base=one
compare=two novalues;
run;

Re: Create data set "CompareDifferences" from proc compare.

Reeza — Fri, 15 Oct 2010 23:59:16 GMT

That never really gives me what I want though, so I use the following macro.

*Macro to compare data sets and produce the different columns/types;

%macro compare_data(dtset1, dtset2);

*Import the variable names, type, length and format from the sas dictionary tables;
data dataset1;
set sashelp.vcolumn;
where libname="WORK" and memname="%quote(%upcase(&dtset1))";

name=upcase(name);
keep name type length format;
run;

*Import the variable names, type, length and format from the sas dictionary tables;
data dataset2;
set sashelp.vcolumn;
where libname="WORK" and memname="%quote(%upcase(&dtset2))";

name=upcase(Name);

keep name type length format;
rename type=type2 length=length2 format=format2;
run;

proc sort data=dataset1; by name;
proc sort data=dataset2; by name;
run;

*Compare the variable types, format and names;
data comparison;

format check $50. data_source $50.;
merge dataset1 (in=a) dataset2 (in=b);
by name;

*Initially set all to verification status;
check="1-Check me"; *Should only show up when variables are in diff data sets;

if a and b then do;
if type=type2 and format=format2 then check="4 - Match"; *If matched of least interest;
if format ne format2 then check="3 - DIFF FORMAT"; *If diff format, note the formats so that it can be compared;
if type ne type2 then check= "2- DIFF TYPE"; *if diff types, note so that it can be fixed;
end;

*If variable is in one data set and not another set the dataset name;
if a and not b then data_source="&dtset1.";
else if b and not a then data_source="&dtset2.";
else if a and b then data_source="Both";

*Get rid of all labels;
attrib _all_ label="";
run;

*Sort by preference of order type;
proc sort data=comparison;
by check name;
run;

%mend;

Re: Create data set "CompareDifferences" from proc compare.

Yuriy2301 — Mon, 18 Oct 2010 07:52:59 GMT

Hi,
Thanks for your answer!
Now I understand why I couldn't find how to create this table - it's really show info in uncomfortable way...
I also used some self-made macro to compare tables before, may be I'll merge this my macro with your and I'll have smth. better then proc compare:).

Thanks!