DATA Step, Macro, Functions and more

SAS character data to SAS date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

SAS character data to SAS date

Sorry to ask this question again.

I have the following character data column in a SAS dataset:

 

Exp_c
05/01/2018
04/31/2017
10/31/2017
01/1/1901
01/1/1901
01/1/1901
09/31/2016

 

Column formating is Character Format $12.  Informat $12.  Length 12

 

Using the following lines of code :

 

data want ;  set have  ; 
new_date = input (Exp_c ,ddmmyy10.) ;
format new_date ddmmyy10. ;
Put _ALL_ ;
run ;

 

Some dates convert and others do not.  THe following do not:

 

04/31/2017

10/31/2017

09/31/2016

 

The log message received is:

 

"NOTE: Invalid argument to function INPUT at line 12045 column 12." 

and

"NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been
      set to missing values.
      Each place is given by: (Number of times) at (Line)Smiley SadColumn).
      3 at 12045:12"

 

Any suggestions ?  Thank you


Accepted Solutions
Solution
‎05-27-2017 12:45 PM
Super User
Posts: 19,772

Re: SAS character data to SAS date

Posted in reply to rmacarthur

Read those dates, if you can't figure it out see the answer in the spoiler.

 

April 31st 

September 31st

 

Spoiler
These dates don't exist in a calendar. 

View solution in original post


All Replies
Super User
Posts: 19,772

Re: SAS character data to SAS date

Posted in reply to rmacarthur

You've used 

 

ddmmyy

 

Your data appears to be 

 

mmddyy

 

Note that the ones that don't convert have records where the day is greater than 12 which would not be a valid month so it creates the error you're seeing. 

Occasional Contributor
Posts: 15

Re: SAS character data to SAS date

Thank you, we're almost there.  Now, using this code:

 

data want   ;  set have   ; 
new_date = input (Exp_c ,mmddyy10.) ;
format new_date mmddyy10. ;
 run ;

 

There are two dates that still do not convert:

04/31/2017

09/31/2016

 

Solution
‎05-27-2017 12:45 PM
Super User
Posts: 19,772

Re: SAS character data to SAS date

Posted in reply to rmacarthur

Read those dates, if you can't figure it out see the answer in the spoiler.

 

April 31st 

September 31st

 

Spoiler
These dates don't exist in a calendar. 
Occasional Contributor
Posts: 15

Re: SAS character data to SAS date

HaHa, data entry error, very good !

Super User
Super User
Posts: 7,039

Re: SAS character data to SAS date

Posted in reply to rmacarthur

That is one of the major problems with collecting dates in character columns.

Users can enter values that are not actual dates.

How to Remember How Many Days Are in Each Month

Rhyme to remember number of days in each month:

30 days has September,
April, June, and November.
When short February's done
All the rest have 31...

rmacarthur wrote:

Thank you, we're almost there.  Now, using this code:

 

data want   ;  set have   ; 
new_date = input (Exp_c ,mmddyy10.) ;
format new_date mmddyy10. ;
 run ;

 

There are two dates that still do not convert:

04/31/2017

09/31/2016

 


 

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 170 views
  • 1 like
  • 3 in conversation