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

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

acemanhattan — Thu, 26 Sep 2019 22:00:25 GMT

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;

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

ChrisNZ — Thu, 26 Sep 2019 22:51:35 GMT

Like this?

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

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

acemanhattan — Fri, 27 Sep 2019 03:01:22 GMT

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.

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

ChrisNZ — Fri, 27 Sep 2019 03:13:17 GMT

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

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

sustagens — Fri, 27 Sep 2019 03:24:07 GMT

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;

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

ChrisNZ — Fri, 27 Sep 2019 05:15:32 GMT

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

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

Patrick — Fri, 27 Sep 2019 05:30:52 GMT

@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;

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

acemanhattan — Fri, 27 Sep 2019 16:31:44 GMT

Thanks @Patrick this worked perfectly.