External file contains invalid date!

Reply
Contributor
Posts: 34

External file contains invalid date!

Hello,

 

I am trying to deal with an issue when trying to read some external from SAS.

 

The problem is that the external file contains invalid date and when I read from SAS it is giving me the following error:

 

ERROR: Data contains invalid content for date datatype. Invalid content is 07/00/20.

 

I have around 20 variables in one single file and  I read from multiple files.

 

So I would really appreciate for any help to fix this issue

 

Thanks in Advance!

Super User
Posts: 19,822

Re: External file contains invalid date!

Fix the code.

If you want help with that you may want to show the code you used. The log is helpful as well. 

Contributor
Posts: 34

Re: External file contains invalid date!

Following is the log

 

NOTE: Copying ABC.DateReported to WORK.DATEREPORTED (memtype=DATA).
NOTE: System Options for BUFSIZE and REUSE were used at user's request.
NOTE: Libname and/or system options for compress, pointobs, data representation and encoding attributes were used at user's request.
ERROR: Data contains invalid content for date datatype. Invalid content is 07/00/20.
occurred at or near line 40, column 70
Super User
Super User
Posts: 7,060

Re: External file contains invalid date!


jjames1 wrote:

Following is the log

 

NOTE: Copying ABC.DateReported to WORK.DATEREPORTED (memtype=DATA).
NOTE: System Options for BUFSIZE and REUSE were used at user's request.
NOTE: Libname and/or system options for compress, pointobs, data representation and encoding attributes were used at user's request.
ERROR: Data contains invalid content for date datatype. Invalid content is 07/00/20.
occurred at or near line 40, column 70

What is the ABC libref pointing to?  If it is an external database then it lied to SAS about what type to use for one of the fields. It told SAS it was going to send DATE values and then it sent something with zeros for the month (or is that the day of the month).

 

If ABC.DateReported is a SAS7BDAT file then it was clearly not created by SAS.

 

Super User
Super User
Posts: 7,060

Re: External file contains invalid date!

If it is an external datbase try the DBSASTYPE= dataset option to tell it to bring that field(s) over as character strings.

 

Another posibility is that your input dataset was a view and that the view definition is trying to create a date value somehow.

Contributor
Posts: 34

Re: External file contains invalid date!

Hello

ABC is the library name and DateReported is the xml attribute

Super User
Super User
Posts: 7,060

Re: External file contains invalid date!

You can't really have a date without the month.

Just tell SAS to read that field as character and then you can convert the value values to dates if you need.

date = input(chardate,mmddyy10.);

You can suppose the notes about invalid values.

date = input(chardate,??mmddyy10.);

 

Super User
Posts: 11,343

Re: External file contains invalid date!

Are you reading this from another DBMS? Since SAS only has numeric and character data "types" the error you post seems to indicate that you may be accessing a something other than a text file.

So what type of file, what code did you use and show the entire log with the proc/data step that generated the error along with the error messages.

Contributor
Posts: 34

Re: External file contains invalid date!

Thank you for the reply.

 

My input file is XML.

 

My code is having issue with only some particular set of XML files . It is working for all others, so I just want to handle this exception for these files

Super User
Posts: 11,343

Re: External file contains invalid date!


jjames1 wrote:

Thank you for the reply.

 

My input file is XML.

 

My code is having issue with only some particular set of XML files . It is working for all others, so I just want to handle this exception for these files


One of my first bits would be to contact the source of the files and tell them they are generating improper dates. Then a request to fix the data file.

Otherwise you're likely going to have to  write a custom XML parser. It might help to show the code you are using to read the file to see what options may be available that you have not used.

Contributor
Posts: 34

Re: External file contains invalid date!

First I read my XML file without using an XML mapper like

filename ABC temp;
libname ABC xmlv2 '/folders/myfolders/temp.xml' automap=replace xmlmap=ABC ;

proc copy in=ABC out=work noclone;
run;

 

This creates the dataset I want to work on.

 

 

Super User
Super User
Posts: 7,060

Re: External file contains invalid date!


jjames1 wrote:

First I read my XML file without using an XML mapper like

filename ABC temp;
libname ABC xmlv2 '/folders/myfolders/temp.xml' automap=replace xmlmap=ABC ;

proc copy in=ABC out=work noclone;
run;

 

This creates the dataset I want to work on.

 

 


Have it write the auto generated XMLMAP file to a permanent file. Edit the permanent file to read the offending field as character instead of DATE.  The re-read the XML file using the fixed map file.  Once you have the data in a SAS dataset as character you can make up your own logic for what to do with the zeros.

Contributor
Posts: 34

Re: External file contains invalid date!

Thank you for your reply.

 

As a beginner to SAS, I will really appreciate if you  Could give me an example on how to do this ?

 

Thanks in advance!

 

 

 

 

 

 

Super User
Super User
Posts: 7,060

Re: External file contains invalid date!

Here is link to SAS page with links to documentation on XML processing in SAS.

http://support.sas.com/rnd/base/xmlengine/

 

The little XMLMAP editing I have done is pretty straight forward, but it depends a lot on the complexity of the XML. 

If you change your code like this then you are creating permanent file with the XMLMAP instructions.  

 

filename ABC '/folders/myfolders/temp.xmlmap';
libname ABC xmlv2 '/folders/myfolders/temp.xml' automap=replace xmlmap=ABC ;

proc copy in=ABC out=work noclone;
run;

Once you have fixed the map file then use this code to re-read the XML file.

filename ABC '/folders/myfolders/temp.xmlmap';
libname ABC xmlv2 '/folders/myfolders/temp.xml' xmlmap=ABC ;

proc copy in=ABC out=work noclone;
run;
Ask a Question
Discussion stats
  • 13 replies
  • 232 views
  • 0 likes
  • 4 in conversation