Help using Base SAS procedures

Character date variable not recognized by SAS date informat or format

Accepted Solution Solved
Reply
Occasional Contributor eap
Occasional Contributor
Posts: 11
Accepted Solution

Character date variable not recognized by SAS date informat or format

 

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 #

 


Accepted Solutions
Solution
‎11-11-2016 02:49 PM
Respected Advisor
Posts: 4,654

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

[ Edited ]

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;

PG

View solution in original post


All Replies
Solution
‎11-11-2016 02:49 PM
Respected Advisor
Posts: 4,654

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

[ Edited ]

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;

PG
Occasional Contributor eap
Occasional Contributor
Posts: 11

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 240 views
  • 1 like
  • 2 in conversation