Character date variable not recognized by SAS date informat or format

eap — Fri, 11 Nov 2016 03:09:26 GMT

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 #

Re: Character date variable not recognized by SAS date informat or format

PGStats — Fri, 11 Nov 2016 04:37:50 GMT

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;

Re: Character date variable not recognized by SAS date informat or format

eap — Fri, 11 Nov 2016 19:54:32 GMT

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

topic Re: Character date variable not recognized by SAS date informat or format in SAS Procedures

Character date variable not recognized by SAS date informat or format

Re: Character date variable not recognized by SAS date informat or format

Re: Character date variable not recognized by SAS date informat or format