topic Re: Find out unique accounts in two different tables in SAS Programming

Find out unique accounts in two different tables

Madhav4114 — Fri, 03 Mar 2023 13:30:16 GMT

Table 1.
Newkey period
1. 201701
2. 201701
3. 201701
1. 201801

Table 2
Newkey. Period
1. 201701
2. 201701
1. 201801

Compare two tables and find out accounts(newkey) which are unique or not present in another table on each period.

Re: Find out unique accounts in two different tables

PeterClemmensen — Fri, 03 Mar 2023 13:39:51 GMT

Try this

data Table1;
input Newkey period;
datalines;
1 201701
2 201701
3 201701
1 201801
;

data Table2;
input Newkey Period;
datalines;
1 201701
2 201701
1 201801
;

data notpresent;

   if _N_ = 1 then do;
      dcl hash h(dataset : 'Table2');
	  h.definekey('Newkey', 'period');
	  h.definedone();
   end;
   
   set Table1;
   
   if h.check();

run;

Re: Find out unique accounts in two different tables

yabwon — Fri, 03 Mar 2023 13:43:10 GMT

1) what did you try so far?

2) what is your expected result?

3) if you wan us to solve your homework at least prepare test data in form we could use them (i.e., data step with datalines)

Bart

Re: Find out unique accounts in two different tables

Madhav4114 — Fri, 03 Mar 2023 13:53:25 GMT

Tried with fuller outer join but unsuccessful with results

Re: Find out unique accounts in two different tables

MayurJadhav — Fri, 03 Mar 2023 13:55:31 GMT

You can try this and adjust the code according to your requirement. @Madhav4114

data Table1;
	input Newkey period;
	datalines;
1 201701
2 201701
3 201701
1 201801
;

data Table2;
	input Newkey Period;
	datalines;
1 201701
2 201701
1 201801
;
run;

proc sort data=Table1;
	by Newkey;
run;

proc sort data=Table2;
	by Newkey;
run;

data in_Table1 in_Table2 in_Both;
	merge Table1(in=a) Table2(in=b);
	by Newkey;

	if a and not b then
		output in_Table1;

	if b and not a then
		output in_Table2;

	if a and b then
		output in_Both;
run;

proc print data=in_Table1;
proc print data=in_Table2;
proc print data=in_Both;
run;

Re: Find out unique accounts in two different tables

Madhav4114 — Fri, 03 Mar 2023 17:30:11 GMT

@MayurJadhav solution looks fine but taking a lot of time to run
P.s dataset size more than 5gb