topic Re: compare column in two datasets and output count in SAS Procedures

compare column in two datasets and output count

hdg — Thu, 17 Apr 2014 18:15:15 GMT

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);

Re: compare column in two datasets and output count

Peter_C — Fri, 18 Apr 2014 07:15:33 GMT

Have you looked at PROC COMPARE

Re: compare column in two datasets and output count

Karthikeyan — Fri, 18 Apr 2014 12:30:02 GMT

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!!

Re: compare column in two datasets and output count

Tom — Fri, 18 Apr 2014 12:41:03 GMT

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 ;