BookmarkSubscribeRSS Feed
jjames1
Fluorite | Level 6

Hello,

 

I am still trying to deal with the issue of fixing the invalid dates when trying to read some XML files from SAS. I use the following code to read my xml files

 

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

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

I thank all of the solutions suggested by everyone, but I could not make any of them to work.

 

Still SAS  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.

 

I have some solutions that I am thinking of

1. Force read the invalid values from SAS when doing the xml mapping

2. Remove the /00 from these attributes when reading as in my case I am bothered of only the month and year

3. Modify the XML parser inorder to read these dates as it is 

 

 

As I am not an expert in SAS, I would really appreciate for any ideas on how to approach these suggested solution or anything else  inorder to fix this issue

 

Thanks in Advance!

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

What does 07/00/20

 

Actually mean? 20Jul2000?  Not sure there is a format off the shelf to handle such an occurence - never seen a date represented like this.  You may need a picture format setup for it, or read it in as text, then process it:

date=mdy(input(scan(c_date,1,"/"),best.),input(scan(c_date,3,"/"),best.),input(scan(c_date,2,"/"),best.));
jjames1
Fluorite | Level 6

This is of the format MM/DD/YYYY which is obviously invalid as there is no day with value 00 

So this shows me the error invalid date, which I am trying fix now

RW9
Diamond | Level 26 RW9
Diamond | Level 26

@jjames1 wrote:

This is of the format MM/DD/YYYY which is obviously invalid as there is no day with value 00 

So this shows me the error invalid date, which I am trying fix now


You can't "fix" this as such.  SAS's variables are strongly typed, so you cannot put an invalid date into a date variable.  Therefore you need to read that in as text.

jjames1
Fluorite | Level 6

But how can I read it as a text when all the mapping is done automatically?

 

ballardw
Super User

@jjames1 wrote:

But how can I read it as a text when all the mapping is done automatically?

 


I believe the previous time you asked this question the suggestion was to save the map file, edit it for the problem variables to create text variables and then parse the resulting text. Did you try that approach?

 

Or is this an endemic problem where you are getting many different structured xml files with the same data problem? In which case it may well be worth working up the chain of the data to find out what is putting invalid dates into a "date" field. It will help if you have worked through one example of correcting the xml map , importing the data and correcting the issue. Record the time involved to point out to managment the costs involved in fixing something that IMHO is a "shoot yourself in the foot" type of error.

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
  • 5 replies
  • 1603 views
  • 0 likes
  • 3 in conversation