05-20-2016 11:46 AM
I am sorry I am posting a question already asnwered many times but none of the solutions suggested are working in my case.Pls help!!
I am trying to find the difference in date of entry to date of death. I created new formatted varaibles entry and death to convert date to numeric and then find the difference but as you can see from the results for lenfol(length of followup) I am not getting numbers as I want. I should have a value in lenfol for all patients but pid=4. Thanks so much.
data final; set sh.final; entry = input(date_of_entry, mmddyy8.); death = input(date_of_death , mmddyy8.); lenfol=death - entry +1; run;
05-20-2016 11:52 AM
05-20-2016 12:12 PM
It looks like your data is mostly in the form of day month year. I would confirm that with the data source if at all practical as the anydtdt informat will likely get incorrect values for a large number of dates.
05-20-2016 02:49 PM
I re-entered the data for dates removing every - with a slash but I guess there is some further formatting in excel that does not allow me to change 2012 to just 12. I googled it but didn't find an easy solution. I re ran the codes and here is the output. It does not seem right. I gave it a format ddmmyy8. .
entry = input(date_of_entry, ddmmyy8.); death = input(date_of_death , ddmmyy8.);
Just looking at second entry how can I get - value , eben when death date was Dec 19 and Entry date was 26/11 for same year. Also for id 1 same format of date is giving me no value in entry while it is giving value in id 2. Please help me understand.
05-20-2016 03:40 PM
Since Excel is CELL focused and SAS is Column oriented then one of the things that sometimes makes Excel to SAS work better is to ensure that the same cell format is applied to all cells in a column.
One of the obnoxious things with Excel is that somethings as entered become a numeric value with an Excel date or time format while others are character values.
Try this for example: 1) in a new Excel spreadsheet type: 1/1/2016 then move to another cell. 2) return to that Cell and look at the format (Right click, select format cells, go to the number tab). It will be a DATE type with a date format on the right you could pick.
3) go to a different cell in same column and type '1/1/2016 (note the apostrophe) 4) do the same examination, You will see a "general" format.
Interesting results when importing that sheet into SAS will result depending on approach taken.
One thing you can try: highlight the entire column in your sheet and set the format to NUMBER (not date). Any character values will be noticeable due to dashes or /. Retype those as 1/1/2015 without the apostrophe any will see those appear as a number (due to the previous setting for the column). After you have addressed any of those changes needed in your data then reset the entire column format to date. If you have multiple date fields it is recommended to set them all to the same appearance. Then import the resultant file.
(or save to CSV and import that one).
Of course if you have lots of records this may not be practical in which case more creativity is needed. But you really do need to look out for data that is mixing mm/dd/yy and dd/mm/yy formats in the same column.