Converting a text DOB into a sas date value

[Tom]

I suspect that your real issue here is that you imported some data from an Excel file and the column that has the date values also has some cells that have character values instead.  Excel is a spreadsheet so you can put any type of data into any cell.  But SAS datasets require that a variable has the same type of data for every observation.  You cannot convert arbitrary character strings into numbers but it can convert numbers into character strings so when your column in the spreadsheet has mixed cell types the variable in SAS will be character.  So the numbers Excel uses to store dates will be stored as digit strings representing that number in the SAS variable.  So you need to first convert the string into a number and then adjust the number to account for the difference in how Excel and SAS count days.

data test;
  input dob $19. ;
  if verify(dob,' 0123456789') then date=input(dob,anydtdte32.);
  else date=input(dob,32.)+'30DEC1899'd ;
  format date date9.;
cards;
38407
29738
42385
37471
34608
26080
39334
37308
38671
27413
04/03/1861 00:00:00
;

Obs    dob                         date

  1    38407                  24FEB2005
  2    29738                  01JUN1981
  3    42385                  16JAN2016
  4    37471                  03AUG2002
  5    34608                  01OCT1994
  6    26080                  27MAY1971
  7    39334                  09SEP2007
  8    37308                  21FEB2002
  9    38671                  15NOV2005
 10    27413                  19JAN1975
 11    04/03/1861 00:00:00    03APR1861

[jimbarbour]

Good catch, @Tom.  I noticed the dates were in the future, but I just put it down to test data.

There's a nice explanation of what's going on in Excel vs. SAS dates here on SAS communities:

Many of us have to convert Excel files into SAS datasets. Converting date/time value from Excel to SAS can be a hassle as both Excel and SAS have different reference dates (i.e. Day 0 in SAS is 1 January 1960 and Day 0 in Excel is 1 January 1900), below formulas offer great help in terms of converting date/time values from Excel to SAS.

• SAS_date = Excel_date - 21916;
• SAS_time = Excel_time * 86400;
• SAS_date_time = (Excel_date_time - 21916) * 86400;

NOTE: Excel has two different datetime "systems" and the default differs depending on the platform. The above formulas assume Excel is using the 1900 date system. If Excel is using the 1904 Date System, use 20454 in the above formulas instead of 21916.

For additional information on the Excel Date Systems, see MS KB article titled XL: The 1900 Date System vs. the 1904 Date System. The 1900 Date system has other well-known quarks, too. For the background of this dual system in the context of a developer's rather funny/scary encounter with Bill Gates, read this blog entry by Joel Spolsky

I think there's an issue with what is considered a leap year as well.  For example, SAS doesn't consider 4000 and 8000 as leap years (they are).  Excel considers 1900 to be a leap year (even though it is not) because MS wanted to maintain compatibility with Lotus 123.  The 21916 number is generally going to take care of the conversion.  SAS date '30DEC1899'd is -21916.  The reason I believe that 30DEC1999 is used instead of 31DEC1999 is because Excel considers 1900 to be a leap year but SAS does not.

Jim

[wlierman]

Hi Tom and Jim,

I am going to run this code against the data.  Very cogent explanation of the nuances of how Excel and SAS interact.

After the test, I'll circle back with the results and mark solution.

Thank you.

wlierman

[jimbarbour]

@wlierman 

After the test, I'll circle back with the results and mark solution

I think the solution has already been marked, but @Tom deserves credit for catching the Excel to SAS issue.  I guess you could create a new topic, "code needed to convert Excel dates to SAS," and let @Tom answer so that he gets credit.  Of course, @Tom is already a Super User, so one more solution may not be quite as big of a deal to him as it might be to someone else.  I will say, however, thank you for being good about following up.  I hate it when I give a carefully worded answer and the person takes it and never responds and never marks the topic as solved.  Even if someone else gets the credit, I'd rather see that than never hearing from the original poster again.

Jim

[jimbarbour]

@wlierman,

Not to dissuade you from finding your own coding style and solution, but here's one possible solution:

DATA	WORK.Merge_data_E_18_c ;
	Set	WORK.Merge_data_E_18(RENAME=(DOB=Old_DOB)); 
	IF	INPUT(Old_DOB, 5.)					THEN
		DO;
			DOB			=	INPUT(Old_DOB, 5.)	+	'30DEC1899'd;
			Fmt_DOB		=	PUT(DOB, MMDDYYS10.);
		END;
	ELSE
		DO;
			DOB			=	DATEPART((INPUT(Old_DOB, ANYDTDTE19.) * 86400));
			Fmt_DOB		=	PUT(DOB, MMDDYYS10.);
		END;
	IF STRIP(Fmt_DOB)	=	'.'				THEN
		Fmt_DOB 		=	' ';
RUN;

The results look like:

You'd probably want to drop "Old_DOB," but I left it in for now so you can see:

• The original Excel value
• The numeric SAS equivalent of the original
• The formatted appearance of the SAS numeric value.

Notice that an invalid date (the very last example) results in missing values.

Jim