topic Proc compare a common variable in two data sets in SAS Programming

Proc compare a common variable in two data sets

di_niu0 — Wed, 04 Aug 2021 21:23:15 GMT

Hi All,

I have two data sets and would like to see the number of common IDs, number of IDs in data set 1 but not 2 and number of IDs in data set 2 but not 1. How should I write the

PROC COMPARE. Thank you.

Data Set 1:

1937

3947

6932

7059

9960

9999

Data Set 2:

1937

3947

4509

7059

8394

8899

9304

Re: Proc compare a common variable in two data sets

jimbarbour — Wed, 04 Aug 2021 21:57:33 GMT

Code OUT= but not OUTBASE, OUTCOMP, or OUTDIFF. The basic stats produced by a Compare will give you the record count for the first dataset (well, both, but you asked about the first).

Sample code, below. Results below that. This code is borrowed from a macro I wrote for something else; you don't need to use a macro, but it's helpful if you have multiple datasets to compare. The results will be in WORK.Set_1c

%LET	Old_Lib		=	WORK;
%LET	New_Lib		=	WORK;

%LET	Old_Data	=	Set_1;
%LET	New_Data	=	Set_2;

%LET	ID			=	ID;

**------------------------------------------------------------------------------**;

&Null	%MACRO	Compare_Datasets(Old_Lib, Old_Data, New_Lib, New_Data, ID=);
	PROC	SORT	DATA	=	&Old_Lib..&Old_Data
					OUT		=	WORK.&Old_Data.o;
		BY	&ID;
	RUN;

	PROC	SORT	DATA	=	&New_Lib..&New_Data
					OUT		=	WORK.&New_Data.n;
		BY	&ID;
	RUN;

	PROC	COMPARE	BASE	=	WORK.&Old_Data.o
					COMPARE	=	WORK.&New_Data.n
					OUT		=	WORK.&Old_Data.c	(DROP=_TYPE_ _OBS_)
					NOVALUES	LISTVAR
					;
		ID	&ID;
	RUN;
%MEND	Compare_Datasets;

%Compare_Datasets(&Old_Lib, &Old_Data, &New_Lib, &New_Data, ID=&ID);

Results:

Jim

Re: Proc compare a common variable in two data sets

Reeza — Wed, 04 Aug 2021 22:33:13 GMT

A data step or SQL merge also work quite well here.

data want;
merge data1(keep=ID in = d1) data2 (keep=ID in=d2);
by ID;

length flag $4.;

if d1 and d2 then flag="Both;
else if d1 then flag = "D1";
else if d2 then flag = "D2";

run;