External file contains invalid date!

[jjames1]

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!

[Reeza]

Fix the code.

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

[jjames1]

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

[Tom]

---

@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.

[Tom]

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.

[jjames1]

Hello

ABC is the library name and DateReported is the xml attribute

[Tom]

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.);

[ballardw]

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.

[jjames1]

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

[ballardw]

---

@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.

[jjames1]

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.

[Tom]

---

@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.

[jjames1]

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!

[Tom]

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;