BookmarkSubscribeRSS Feed
hwangnyc
Quartz | Level 8

Hi everyone,

 

I have two data sets with an identifier variable. I would like to know which ID variables are not in the base set: So my data is this:

 

DATA cars1;
 INPUT make $ group ID;
CARDS;
AMC   1 123
AMC     1 123
AMC    1 123
Buick   1 456
Buick   1 456

;
RUN;  

DATA cars2;
 INPUT make $   group ID;
CARDS;
AMC   1 123
AMC     1 123
AMC    1 123
Buick   1 456
Buick   1 456
Toyota   2 789
Honda   1 780
;
RUN; 

I would like to have proc compare tell me that ID 780 and 789 are not in Cars 1.

 

Additionally, would I be able to use a where statement to restrict the comparison to only group 1? For example, I want to know which ID is not in the cars2 dataset restricted on only data that is part of group 1. The result would be 780.

 

Thanks in advance!

 

2 REPLIES 2
Shmuel
Garnet | Level 18

PROC COMPARE is very efficient to check are two tables identical and if negative

which variables in which observations are not equal, displaying theeir values.

 

To do what you want is very easy by sql:

proc sq;
    create table diff as  /* <<< optional >>> */
    select distinct ID from table_2 
    where ID not in (select distinct ID from table_1);
quit;
   
emrancaan
Obsidian | Level 7

use following code instead of PROC COMPARE.

 

Proc sort data=CARS1; by ID; Run;
Proc sort data=CARS2; by ID; Run;

data Difference;
merge CARS1(in=A) CARS2(in=B);
by ID MAKE Group;
length inds $ 40;
if A and not B then do;
inds='CARS1';
output;
end;
if not A and B then do;
inds='CARS2';
output;
end;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 628 views
  • 4 likes
  • 3 in conversation