Note the differences in the 4th record with the date. Which data set is correct to be read in?
This works fine for me, assuming the last data is correct.
Given what you've posted to me privately though, I suspect this also won't work as its not the issue you're looking to solve.
data have;
informat PatNO $3. ADM_DATE date9. age 8. Sex $1.;
format adm_date date9.;
input PatNo Adm_Date AGE Sex ;
cards;
101 12Jan2013 45.00 .
101 26Feb2016 . F
101 07Mar2018 . .
251 05Nov2015 66.00 M
251 01Jan2016 . .
251 23Jul2018 . .
251 09Jun2020 . .
;;;;
run;
*create master list;
proc sql;
create table want as
select patNO, adm_date, coalesce(age, max(age)) as age, coalesce(sex, max(sex)) as sex
from have
group by PATNO
order by 1, 2;
quit;
proc print data=want;
run;
@Bluejags wrote:
Sorry, in the last post I had not formatted the dates/data properly. I was tried to use the RETAIN/COUNTER statement to get the Age from the second, third, and fourth adm_Dates. I ended up getting the difference between second and first, third and second dates, and so on in a new column I labelled (Age_) with the RETAIN statement. As for assigning the Sex to the missing rows, I followed codes as suggested by @Reeza and @Ksharp ; the Sex column were filled correctly, but the first row for second patient (251) went missing form the final output, and instead, the second row of that patient was duplicated. Wondering if this is because I am new to SAS, not correctly following the coding process.
PatNo Adm_Date AGE Sex
101 12Jan2013 45.00 .
101 26Feb2016 . F
101 07Mar2018 . .
251 05Nov2015 66.00 M
251 01Jan2016 . .
251 23Jul2018 . .
251 09Jun2020 . .
Thanks.
