BookmarkSubscribeRSS Feed
jjames1
Fluorite | Level 6

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!

13 REPLIES 13
Reeza
Super User

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
Fluorite | Level 6

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
Super User Tom
Super User

@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
Super User Tom
Super User

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
Fluorite | Level 6

Hello

ABC is the library name and DateReported is the xml attribute

Tom
Super User Tom
Super User

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
Super User

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
Fluorite | Level 6

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
Super User

@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
Fluorite | Level 6

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
Super User Tom
Super User

@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
Fluorite | Level 6

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
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1179 views
  • 0 likes
  • 4 in conversation