Filtering Output

[Solly7]

Hi,

I need help, i need to only return rows in table2 which are not available in table1  by 'policy_no and date'

please not that all the rows in table1 are available in table2. so i need to exclude them from table2. date format is yyyymm(year month)

                  Table1                                                                                                                                                                                 

policy_no     date          amount                             

1                  201901       500

2                  201901      1000

3                  201901       500

4                  201901       500

5                  201901       300

                        Table2

policy_no     date          amount                             

1                  201901       500

1                  201902       500

2                  201902       1000

2                  201901      1000

3                  201901       500

4                  201901       500

5                  201901       300

6                  201901       200

7                  201901       500

          Data Want( below is the output i need)

policy_no     date          amount   

1                  201902       500

2                  201902      1000               

6                  201901       200

7                  201901       500

[ed_sas_member]

Hi @Solly7 

Here is a way to achieve this, using a merge:

proc sort data=Table1 out=Table1_sorted; by policy_no date; run;
proc sort data=Table2 out=Table2_sorted; by policy_no date; run;

data want;
	merge Table1_sorted (in=x) Table2_sorted (in=y);
	by policy_no date;
	if y and not x;
run;

[Ksharp]

proc sql;
select * from table2
except
select * from table1;
quit;

[novinosrin]

Hi @Solly7  Considering POLICY_NO & DATE are the key variables to check, the combination makes it a convenient - composite unique key. You could use a simple HASH check method.

data one;
input policy_no     date  :yymmn6.        amount      ;
format date yymmn6.;
cards;
1                  201901       500
2                  201901      1000
3                  201901       500
4                  201901       500
5                  201901       300
;

data two;
input policy_no     date  :yymmn6.        amount      ;
format date yymmn6.;
cards;
1                  201901       500

1                  201902       500

2                  201902       1000

2                  201901      1000

3                  201901       500

4                  201901       500

5                  201901       300

6                  201901       200

7                  201901       500
;



data want ;
 if _n_=1 then do;
   dcl hash H (dataset:'one') ;
   h.definekey  ("policy_no","date") ;
   h.definedata ("policy_no","date") ;
   h.definedone () ;
 end;
 set two;
 if h.check() ne 0;
run;

policy_no	date	amount
1	201902	500
2	201902	1000
6	201901	200
7	201901	500

[novinosrin]

And should HASH cause memory issues should you not have adequate memory to store table ONE in the HASH memory resident table, you could conveniently resort to Proc SQL JOIN as a look up tool

data one;
input policy_no     date  :yymmn6.        amount      ;
format date yymmn6.;
cards;
1                  201901       500
2                  201901      1000
3                  201901       500
4                  201901       500
5                  201901       300
;

data two;
input policy_no     date  :yymmn6.        amount      ;
format date yymmn6.;
cards;
1                  201901       500

1                  201902       500

2                  201902       1000

2                  201901      1000

3                  201901       500

4                  201901       500

5                  201901       300

6                  201901       200

7                  201901       500
;
proc sql;
create table want as
select a.*
from two a left join one b
on a.policy_no=b.policy_no and a.date=b.date
where missing(b.policy_no);
quit;


proc print noobs;run;

policy_no	date	amount
1	201902	500
2	201902	1000
6	201901	200
7	201901	500