BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jade_SAS
Pyrite | Level 9

Hi All,

 

    I saw from the documentation the ID variable can be multiple variables (see my example var1-var3). My question is whether the ID variables (var1-var3) has to be unique (which means distinct in base and compare table)? thank you!

 

Proc compare base = Table1 compare = Table2;

ID var1 var 2 var3;

Run;

 

Jade

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

You had me curious, and I couldn't be bothered reading the documentation, so I set up a quick test.

 

Yes, and you'll get a warning message. Run the following code.

 

By the way, I frequently have questions like this, and I find it a lot faster to just set up a quick test than to try to get an answer from the communities. As your SAS skills grow, give it a try!

 

Tom

 

/* 3 keys are unique */
data Apples;
	input var1 var2 var3 var4 var5;
	cards;
1 3 8 8 14
2 0 7 3 9
3 0 4 7 13
3 4 2 14 2
4 0 10 7 7
4 5 9 14 9
4 10 6 0 11
4 14 5 8 4
5 6 7 6 1
6 12 1 0 5
9 7 3 9 13
10 0 13 11 0
12 3 7 6 11
14 0 1 6 10
14 12 11 11 8
run;

data Oranges;
	input var1 var2 var3 var4 var5;
	cards;
1 3 8 8 14
2 0 7 3 9
3 0 4 7 13
3 4 2 12 12
4 0 10 12 11
4 5 9 8 5
4 10 6 2 6
4 14 5 7 13
5 6 7 11 11
6 12 1 0 5
9 7 3 9 13
10 0 13 11 0
12 3 7 6 11
14 0 1 6 10
14 12 11 11 8
run;

proc compare base=Apples compare=Oranges;
	id var1 var2 var3;
run;

/* 4th record is duplicate key */
data Apples;
	input var1 var2 var3 var4 var5;
	cards;
1 3 8 8 14
2 0 7 3 9
3 0 4 7 13
3 0 4 14 2
4 0 10 7 7
4 5 9 14 9
4 10 6 0 11
4 14 5 8 4
5 6 7 6 1
6 12 1 0 5
9 7 3 9 13
10 0 13 11 0
12 3 7 6 11
14 0 1 6 10
14 12 11 11 8
run;

data Oranges;
	input var1 var2 var3 var4 var5;
	cards;
1 3 8 8 14
2 0 7 3 9
3 0 4 7 13
3 0 4 12 12
4 0 10 12 11
4 5 9 8 5
4 10 6 2 6
4 14 5 7 13
5 6 7 11 11
6 12 1 0 5
9 7 3 9 13
10 0 13 11 0
12 3 7 6 11
14 0 1 6 10
14 12 11 11 8
run;

proc compare base=Apples compare=Oranges;
	id var1 var2 var3;
run;

 

View solution in original post

2 REPLIES 2
TomKari
Onyx | Level 15

You had me curious, and I couldn't be bothered reading the documentation, so I set up a quick test.

 

Yes, and you'll get a warning message. Run the following code.

 

By the way, I frequently have questions like this, and I find it a lot faster to just set up a quick test than to try to get an answer from the communities. As your SAS skills grow, give it a try!

 

Tom

 

/* 3 keys are unique */
data Apples;
	input var1 var2 var3 var4 var5;
	cards;
1 3 8 8 14
2 0 7 3 9
3 0 4 7 13
3 4 2 14 2
4 0 10 7 7
4 5 9 14 9
4 10 6 0 11
4 14 5 8 4
5 6 7 6 1
6 12 1 0 5
9 7 3 9 13
10 0 13 11 0
12 3 7 6 11
14 0 1 6 10
14 12 11 11 8
run;

data Oranges;
	input var1 var2 var3 var4 var5;
	cards;
1 3 8 8 14
2 0 7 3 9
3 0 4 7 13
3 4 2 12 12
4 0 10 12 11
4 5 9 8 5
4 10 6 2 6
4 14 5 7 13
5 6 7 11 11
6 12 1 0 5
9 7 3 9 13
10 0 13 11 0
12 3 7 6 11
14 0 1 6 10
14 12 11 11 8
run;

proc compare base=Apples compare=Oranges;
	id var1 var2 var3;
run;

/* 4th record is duplicate key */
data Apples;
	input var1 var2 var3 var4 var5;
	cards;
1 3 8 8 14
2 0 7 3 9
3 0 4 7 13
3 0 4 14 2
4 0 10 7 7
4 5 9 14 9
4 10 6 0 11
4 14 5 8 4
5 6 7 6 1
6 12 1 0 5
9 7 3 9 13
10 0 13 11 0
12 3 7 6 11
14 0 1 6 10
14 12 11 11 8
run;

data Oranges;
	input var1 var2 var3 var4 var5;
	cards;
1 3 8 8 14
2 0 7 3 9
3 0 4 7 13
3 0 4 12 12
4 0 10 12 11
4 5 9 8 5
4 10 6 2 6
4 14 5 7 13
5 6 7 11 11
6 12 1 0 5
9 7 3 9 13
10 0 13 11 0
12 3 7 6 11
14 0 1 6 10
14 12 11 11 8
run;

proc compare base=Apples compare=Oranges;
	id var1 var2 var3;
run;

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1257 views
  • 1 like
  • 2 in conversation