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:
ID
1937
3947
6932
7059
9960
9999
Data Set 2:
ID
1937
3947
4509
7059
8394
8899
9304
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.