compare column in two datasets and output count

[hdg]

I am trying to compare two datasets data1 and data2.

I have the column name in both "var"

and would like to check if there any differences in those two columns in the two datasets

and then count the number of differences

I then would like to call this macro function and save the count to macro variable x

Somehow this doesnt work any help is appreciated. Thanks!ee

%macro CompareUniqueCols(data1, data2, var);

  %* check if all the unique values  in a column var of data set 1 and data set 2 are same and outputs the number of differences;

   proc sql;

create table temp1 as select distinct &var  from &data1 order by &var;

run;

proc sql;

create table temp2 as select distinct &var from &data2 order by &var;

run;

proc compare base = temp2 compare = temp1 outall outnoequal out = outcompare ;

run;

proc sql noprint;

select count(*) into: noCompareObs from outcompare ;

run;

&noCompareObs;

%mend;

%let x = %CompareUniqueCols(data1, data2, var);

[Peter_C]

Have you looked at PROC COMPARE

[Karthikeyan]

Assuming the datasets have only one variable "Var" . Here's the data step way ,

DATA

   DIFF

;

MERGE

DATA1(IN = A)

DATA2(IN = B)

;

BY VAR ;

IF A AND B THEN DELETE ;

ELSE OUTPUT ;

RUN;

you can get the count using your SQL on DIFF

Thanks!!

[Tom]

You are confused about what a macro is.  SAS macro language is a tool to generate code.  In general you cannot treat a macro as if it was a subroutine function.

Here is an example of how you might modify your macro to allow the user to specify the macro variable that you want it use to return the counts.

%macro CompareUniqueCols(data1, data2, var, mvar=x);

%local noCompareObs;

...

%let &mvar=&noCompareObs;

%mend;

%let x=;

%CompareUniqueCols(data1=today,date2=yesterday,var=CustomerID, mvar=X);

%put Result = &x ;