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):(Column).
3 at 12045:12"
Any suggestions ? Thank you
Read those dates, if you can't figure it out see the answer in the spoiler.
April 31st
September 31st
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.
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
Read those dates, if you can't figure it out see the answer in the spoiler.
April 31st
September 31st
HaHa, data entry error, very good !
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.