Find difference between two dates

Reply
Contributor
Posts: 39

Find difference between two dates

Hi

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;

 

SAS Output

Obs PID Date_of_entry entry Date_of_death death lenfol
1 1 3/12/2012 . 19-2-13 . .
2 2 26-11-2012 . 19-12-12 . .
3 3 11/8/2012 . 2/10/2012 . .
4 4 6/9/2012 19153   . .
5 5 22-8-12 . 22-9-12 . .
6 6 16-10-12 . 22-11-12 . .
7 7 4/9/2012 19092 10/11/2012 22199 3108
8 8 17-11-12 . 11/6/2013 . .
9 9 22-11-12 . 25-12-13 .
Esteemed Advisor
Posts: 5,198

Re: Find difference between two dates

Most SAS informats cannot handle inconsistent input.
Take a look at the anydt family informats.
Another option is to parse your dates and fix into a uniform format first.
The best solution is to go to your data provider and ask for data that follows an agreed format.
Data never sleeps
Grand Advisor
Posts: 10,210

Re: Find difference between two dates

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.

 

 

 

 

Contributor
Posts: 39

Re: Find difference between two dates

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.

 

Thanks

SAS Output

Obs PID Date_of_entry entry Date_of_death death lenfol
1 1 3/12/2012 . 19/2/13 19408 .
2 2 26/11/2012 22245 19/12/12 19346 -2898
3 3 11/8/2012 . 2/10/2012 . .
4 4 6/9/2012 19242   . .
5 5 22/8/12 19227 22/9/12 19258 32
6 6 16/10/12 19282 22/11/12 19319 38
7 7 4/9/2012 19240 10/11/2012 22229 2990
8 8 17/11/12 19314 11/6/2013 . .
9 9 22/11/12 19319 25/12/13 19717 399
10 10 15/1/13 19373 1/4/2014 19814 442
Grand Advisor
Posts: 10,210

Re: Find difference between two dates

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.

Ask a Question
Discussion stats
  • 4 replies
  • 272 views
  • 2 likes
  • 3 in conversation