topic Matching variables in 2 different datasets in SAS Programming

Matching variables in 2 different datasets

Havi — Mon, 17 Aug 2015 09:21:31 GMT

Hi Guys

Please assist. What is the most optimal way to check if two variables exist in 2 separate datasets. The file sizes I am dealing with is quite large - around 50million records in each dataset.

Table 1 Table 2

A0001 A0001

A0002 A0003

A0003 A0004

A0004 A0005

A0005

A0006

Output dataset required:

ACCOUNT_ID STATUS

A0001 EXISTS in BOTH TABLES

A0002 DOES NOT EXIST

A0003 EXISTS in BOTH TABLES

A0004 EXISTS in BOTH TABLES

A0005 EXISTS in BOTH TABLES

A0006 DOES NOT EXIST

Thank you for your assistance.

Re: Matching variables in 2 different datasets

RW9 — Mon, 17 Aug 2015 09:34:20 GMT

Well, SQL probably isn't the best for this, so try a datastep merge on distinct values:

proc sort data=data1 out=tmp1 nodupkey;

by account_id;

run;

proc sort data=data2 out=tmp2 nodupkey;

by account_id;

run;

data want;

merge tmp1 (in=a) tmp2 (in=b);

length status $50;

if a and b then status="In Both";

else if a then status="Only in A";

else status="Only in B";

run;

It will take a while to run though just due to the quantity of data.

Re: Matching variables in 2 different datasets

Kurt_Bremser — Mon, 17 Aug 2015 11:25:05 GMT

Are we talking about variables or values?

Re: Matching variables in 2 different datasets

Ksharp — Mon, 17 Aug 2015 12:07:20 GMT

Hash Table .