Good day Please see below for an approach. You should be able to leverage the below code. The approach has three steps: Create a new table with your keys, and derive the length of stay Create a new table where you isolate the claim IDs, this requires context to your work Join the claim IDs onto the base file, be careful here to not duplicate, you will notice I've added a few joining keys to ensure the data comes back correctly proc sql;
create table want_base_file as
select distinct
PERSON_ID
,provider_id
,admission_dt
,max(discharge_dt) format=date9. as final_discharge_dt
,intck('day', admission_dt, calculated final_discharge_dt) as length_of_stay
from Data_Import_20220802
group by PERSON_ID
,provider_id
,admission_dt;
quit;
proc sql;
create table want_claim_id_flag as
select distinct
PERSON_ID
,provider_id
,admission_dt
,claim_id
,(case
when claim_id=12345 then 'A'
when claim_id=12346 then 'D'
else '' end) as claim_flag
from Data_Import_20220802;
quit;
proc sql;
create table want_final as
select distinct
a.*
,b.claim_id as claim_id_adm
,c.claim_id as claim_id_discharge
from want_base_file as a
left join want_claim_id_flag as b
on (a.PERSON_ID=b.PERSON_ID
and a.provider_id=b.provider_id
and a.admission_dt=b.admission_dt
and b.claim_flag='A')
left join want_claim_id_flag as C
on (a.PERSON_ID=c.PERSON_ID
and a.provider_id=c.provider_id
and a.admission_dt=c.admission_dt
and c.claim_flag='D');
quit;
... View more
