Does this get you close? data patients ;
infile datalines dsd delimiter=',';
input patientID $ year $ diagA $ diagB $ diagC $ ;
datalines;
1,2010,,,.
1,2011,,1,1
1,2013,,,
1,2014,1,,
;
run;
title1 'patients' ;
proc print data = patients ;
run;
/*
* get extreme values for each patient
*/
proc sql ;
create table patients_defaults as
select patientID
, prxchange('s/(\d+),\d+/$1/' , -1 , max(case when diagA is not null then CATX(',',year,diagA) else ' ' end) ) AS diagA_default_year
, prxchange('s/\d+,(\d+)/$1/' , -1 , max(case when diagA is not null then CATX(',',year,diagA) else ' ' end) ) AS diagA_default
, prxchange('s/(\d+),\d+/$1/' , -1 , max(case when diagB is not null then CATX(',',year,diagB) else ' ' end) ) AS diagB_default_year
, prxchange('s/\d+,(\d+)/$1/' , -1 , max(case when diagB is not null then CATX(',',year,diagB) else ' ' end) ) AS diagB_default
, prxchange('s/(\d+),\d+/$1/' , -1 , max(case when diagC is not null then CATX(',',year,diagC) else ' ' end) ) AS diagC_default_year
, prxchange('s/\d+,(\d+)/$1/', -1 , max(case when diagC is not null then CATX(',',year,diagC) else ' ' end) ) AS diagC_default
from patients
group by patientID ;
quit;
run;
title1 'patient defaults' ;
proc print data = patients_defaults ;
run;
/*
* apply extreme values to each patient detail
*/
title1 'patients with defaults ' ;
proc sql ;
select p.patientID
, p.year
, case when p.diagA is null then d.diagA_default else p.diagA end AS diagA
, case when p.diagA is null then d.diagA_default_year else p.year end AS diagA_year
, case when p.diagb is null then d.diagb_default else p.diagB end AS diagB
, case when p.diagB is null then d.diagB_default_year else p.year end AS diagB_year
, case when p.diagb is null then d.diagc_default else p.diagC end AS diagC
, case when p.diagC is null then d.diagC_default_year else p.year end AS diagC_year
from patients p
join patients_defaults d
on p.patientID = d.patientID
order by 1,2
;
quit;
run;
... View more
