This is all done in SAS, not excel. Here is the full sas code. Very simple, i'm importing 6 files (I hid the file name), merging 3 'unique' files and 3 'propogated' files across study years, identifying the first procedure (either head CT or blood product), merging, then trying to get time elapsed. The attached file is after the merged step. %macro import (sheet =, year = , DATAFILE=); PROC IMPORT OUT= HS.import_&year._&sheet DATAFILE= &DATAFILE DBMS=XLSX REPLACE; SHEET = &sheet; RUN; %mend import; %import (sheet = unique, year = 2013, DATAFILE = "(hidden)"); %import (sheet = unique, year = 2014, DATAFILE = "(hidden)"); %import (sheet = unique, year = 2015, DATAFILE = "(hidden)"); %import (sheet = prop, year = 2013, DATAFILE = "(hidden)"); %import (sheet = prop, year = 2014, DATAFILE = "(hidden)"); %import (sheet = prop, year = 2015, DATAFILE = "(hidden)"); Data hs.propogated; Set import_2013_prop import_2014_prop import_2015_prop; attrib procdatetime format=datetime19.; procdatetime=input(put(proc_start_date,date9.)||put(proc_start_time,time8.),datetime.); run; proc sort data=hs.propogated; by registry_ procedure descending procdatetime ; run; Data hs.coded_propogated; Set hs.propogated; by REGISTRY_; KEEP REGISTRY_ first_headCT first_blood ; RETAIN first_headCT first_blood ; IF FIRST.REGISTRY_ THEN do; first_headCT = .; first_blood = .; END; if procedure = 'BLOOD' and procdatetime NE . then first_blood=procdatetime; if procedure = 'HEADCT' and procdatetime NE . then first_headct=procdatetime; format first_headct first_blood datetime19.; IF LAST.REGISTRY_ THEN OUTPUT; Run; data hs.coded_unique; set import_2013_unique import_2014_unique import_2015_unique; attrib arrivaldatetime format=datetime19.; arrivaldatetime=input(put(HOSPARRIVALDATE,date9.)||put(HOSPARRIVALTIME,time8.),datetime.); run; proc sort data=hs.coded_propogated; by registry_; run; proc sort data=hs.coded_unique; by registry_; run; data hs.merged; merge hs.coded_propogated hs.coded_unique; by registry_; arrival_to_ct = first_headCT - arrivaldatetime; ct_to_blood = first_headCT - first_blood ; format arrival_to_ct ct_to_blood hhmm.; run; data merged; set hs.merged (keep = registry_ first_headCT first_blood hosparrivaltime hosparrivaldate arrivaldatetime arrival_to_ct ct_to_blood); run; PROC EXPORT DATA= WORK.MERGED OUTFILE= "\\vmware-host\Shared Folders\Desktop\merged.csv" DBMS=CSV LABEL REPLACE; PUTNAMES=YES; RUN;
... View more