Find difference between two dates

[Ruhi]

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	.

[LinusH]

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.

[ballardw]

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.

[Ruhi]

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

[ballardw]

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.