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
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;
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;
Thank you @TomKari
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.
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.