topic Need to fill in missing sex info and count age in New SAS User

Need to fill in missing sex info and count age

Bluejags — Tue, 26 Oct 2021 19:07:52 GMT

Hello, I need to fill in the missing sex info and the age info. For age, I need to fill in with the age at each visit using the Adm_Date.


PatNo  Adm_Date AGE  Sex  
datalines;
101 12Jan2013 45.00   . 
101 26Feb2016 .       F    
A11 07Mar2018 .       .   
251 05Nov/2015 66.00   M   
251 01Jan 2016 .       
251 23Jul2018 .       .       
251 09Jun2020 .        .

Not sure what the best to these.

Thanks.

Re: Need to fill in missing sex info and count age

Bluejags — Tue, 26 Oct 2021 19:59:12 GMT

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.

Re: Need to fill in missing sex info and count age

Reeza — Tue, 26 Oct 2021 20:12:36 GMT

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.

Re: Need to fill in missing sex info and count age

Bluejags — Tue, 26 Oct 2021 20:11:34 GMT

The latest dataset is the correct one @Reeza .

Re: Need to fill in missing sex info and count age

Reeza — Tue, 26 Oct 2021 20:16:21 GMT

Then you should have an answer, see my post above.