topic Filtering Output in SAS Programming

Filtering Output

Solly7 — Tue, 10 Mar 2020 08:31:02 GMT

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

Re: Filtering Output

ed_sas_member — Tue, 10 Mar 2020 08:42:37 GMT

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;

Re: Filtering Output

Ksharp — Tue, 10 Mar 2020 11:29:08 GMT

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

Re: Filtering Output

novinosrin — Tue, 10 Mar 2020 12:16:54 GMT

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

Re: Filtering Output

novinosrin — Tue, 10 Mar 2020 12:22:56 GMT

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