topic Re: How can i compare two variables from different data sets in SAS Programming

How can i compare two variables from different data sets

lixuan — Fri, 21 Jul 2017 08:57:52 GMT

Hi, Maybe a very simple quesion, but it puzzles me. I want to compare two variables with different names in two data sets. How can I do it? for example, I want to compare a in data set one with d in data set two. I check the 'proc compare', but it compares the whole data set. thank you .

data one;
input a
cards;
6
5
6
7
;
run;
data two;
input d;
cards;
7
8
5
2
;
run;

Re: How can i compare two variables from different data sets

PeterClemmensen — Fri, 21 Jul 2017 09:02:15 GMT

What do you mean by compare? Do you want to subtract them from each other? And do you not have some ID variable, this will usually be the case when comparing variables.

Re: How can i compare two variables from different data sets

lixuan — Fri, 21 Jul 2017 09:08:05 GMT

No , I just want to compare the two variables to identify if they can be used as ID which can be used to merge the two data sets.

Re: How can i compare two variables from different data sets

RW9 — Fri, 21 Jul 2017 09:15:06 GMT

" I want to compare two variables with different names in two data sets." - I am sorry, could you explain what you mean/want as output? You say you don't want to compare the whole dataset, so what exactly is it you want to compare? Proc compare would obviously be first choice, just rename d to a:

proc compare base=one compare=two (rename=(d=a));
run;

Re: How can i compare two variables from different data sets

Shmuel — Fri, 21 Jul 2017 09:21:43 GMT

What is the meaning of those two variables? What output you want to get?

You said that:

I check the 'proc compare', but it compares the whole data set.

you can use proc compare as:

proc compare base=one (keep=a)
               compare=two(keep=d rename=(d=a));
run;

If it doesn't satisfy you, explain why and what are you expecting.

Besides you wrote:

 I just want to compare the two variables to identify if they can be used as ID

should't the IDs be unique? How do you know when they fit that they have same meaning and can be used as IDs ?

Re: How can i compare two variables from different data sets

lixuan — Fri, 21 Jul 2017 09:22:47 GMT

Sure , I have two datasets with different variables, and I want to merge both, but I dont konw which variables can be use as ID, so i want to compare the variables.

Re: How can i compare two variables from different data sets

Kurt_Bremser — Fri, 21 Jul 2017 09:31:27 GMT

proc sql;
create table test as
select t1.a, t2.d
from one t1 full join two t2
on t1.a = t2.d
;
quit;

This will give you a picture how the variables match.

Re: How can i compare two variables from different data sets

lixuan — Fri, 21 Jul 2017 10:09:07 GMT

OK, I think proc compare is a better result, because it gives me a summary report. also, by using sql i can get the details. Thank you all very much.

Re: How can i compare two variables from different data sets

lixuan — Fri, 21 Jul 2017 10:15:30 GMT

Your code is helpful. The ID should be unique. I am merging different stock data, but different database has their own ID system, so i want to know if they are same. Thks$ regards

Re: How can i compare two variables from different data sets

ballardw — Fri, 21 Jul 2017 15:36:29 GMT

You didn't show the Proc Compare code you used. If the variables are different names in the two sets you can use something like this:

proc compare base=one compare=two;

var a;

with d;

run;

to restrict the comparison to specific variables and only compare the ones of interest.

Since you looking a suitability as a matching variable you might want to sort the data sets by the candidate variable for better results.

Re: How can i compare two variables from different data sets

lixuan — Fri, 21 Jul 2017 15:44:46 GMT

Thank you , I don't konw proc compare code can be folllowed by var. yours answer is the simpliest.