Selecting Observations from One Table Only When They Match Criteria in Another Table

[acemanhattan]

Below I create two tables, with the second table a subset of the first. What I'd like is to retain only the observations from the first table where the MemberCTG and HCPCS on a given observation in the first table matches the MemberCTG and HCPCS on an observation on the second table. 

My thought was to somehow pass the MemberCTGs from the second table into a macro list and filter the first table on those, then do the same thing with the HCPCs from the second table and filter the first table on that again. But that seems like it ends up being a far too complicated solution for the problem I'm trying to solve.

PROC SQL;
   CREATE TABLE cb.LONGTERMOX09252019_Filter1 AS 
   SELECT t1.ClaimID, 
          t1.LineNum, 
          t1.MemberID, 
          t1.MemberCTG, 
          t1.date, 
          t1.HCPCS, 
          t1.billed, 
          t1.allowed, 
          t1.paid, 
          t1.MR_Cases_Admits, 
          t1.MR_Units_Days, 
          t1.MR_Procs
      FROM cb.LongTermOx09252019 t1
      ORDER BY t1.MemberCTG,
               t1.HCPCS,
               t1.date;
QUIT;

data cb.LONGTERMOX09252019_Filter2 (keep=MemberCTG HCPCS Count);
  set cb.LONGTERMOX09252019_Filter1;
  by MemberCTG HCPCS;
  if first.HCPCS then count=0;
  count+1;
  if last.HCPCS and count > 35 then output;
run;

[ChrisNZ]

Like this?

proc sql;
  select TABLE1.* 
  from TABLE1 
      ,(select unique MemberCTG, HCPCS from TABLE2)
  where TABLE1.MemberCTG = TABLE2.MemberCTG
    and TABLE1.HCPCS     = TABLE2.HCPCS;

[acemanhattan]

The output isn't quite what I'm looking for in the sense that it's not a data set I can export and use for modeling.

[ChrisNZ]

@acemanhattan  I wish there was a SQL keyword to CREATE tables. It would be so nice to be able to CREATE tables. 🙂

[sustagens]

Here is another way of doing it, although it takes two passes at the second table

proc sql;
create table want as(
select t1.* 
from cb.LongTermOx09252019 t1 
where t1.MemberCTG in (select distinct MemberCTG from cb.LONGTERMOX09252019_Filter2) and 
t1.HCPCS in (Select distinct HCPCS from cb.LONGTERMOX09252019_Filter2)
);
quit;

[ChrisNZ]

An in clause takes a lot longer to run than a join.

[Patrick · Accepted solution]

@acemanhattan 

Looking at the code you've posted I believe you could get the desired result directly via a group by/having clause

PROC SQL;
   CREATE TABLE cb.want AS 
   SELECT t1.ClaimID, 
          t1.LineNum, 
          t1.MemberID, 
          t1.MemberCTG, 
          t1.date, 
          t1.HCPCS, 
          t1.billed, 
          t1.allowed, 
          t1.paid, 
          t1.MR_Cases_Admits, 
          t1.MR_Units_Days, 
          t1.MR_Procs
      FROM cb.LongTermOx09252019 t1
      group BY t1.MemberCTG,
               t1.HCPCS
      having count(*) > 35
      ORDER BY t1.MemberCTG,
               t1.HCPCS,
               t1.date;
      ;
QUIT;

[acemanhattan]

Thanks @Patrick this worked perfectly.