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;
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;
Like this?
proc sql;
select TABLE1.*
from TABLE1
,(select unique MemberCTG, HCPCS from TABLE2)
where TABLE1.MemberCTG = TABLE2.MemberCTG
and TABLE1.HCPCS = TABLE2.HCPCS;
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.
@acemanhattan I wish there was a SQL keyword to CREATE tables. It would be so nice to be able to CREATE tables. 🙂
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;
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;
Thanks @Patrick this worked perfectly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.