Character date variable not recognized by SAS date informat or format

[eap]

Hi SAS Community!

Problem:

1) I have multiple Excel files each containing date variables

2) All dates have General format in Excel because the date variables have a mixture of valid dates and acronyms

3) The date variable from one file out of 16 reads in incorrectly. I went back in Excel and specifically formatted the date variable in the problem file as 'date mm/dd/yyyy' but nothing changed.

             Excel                             Reads into SAS before applying an  informat/format                      PROC CONTENTS

File 1) 01/15/2013                               15JAN2013                                                                          datevar   Num  DATE9.

File 2) 01/01/2013                                  41275                                                                                datevar  Char  $5.

The value 41275 is way more than the calculated number of days since Jan 1, 1960 [19359]. I have no idea what units this is in to begin to figure out next steps . 

Using an informat (DATE9. or MMDDYY10.) isn't appropriate nor is just using a format alone. There must be a step in between. What am I missing?

Data want;

Set have;

  datevarN = input(datevar, X.);  

format datevarN X.;

run;

LOG- NOTE: Invalid argument to INPUT function at the line #

[PGStats · Accepted solution]

41275 is a Microsoft Office date value, which is the number of days since 01JAN1900 plus two (don't ask).

So,  SASdate = MSOdate + '01JAN1900'd - 2;

[eap]

A big thank you to you! I would have never figured this out!