DATA Step, Macro, Functions and more

How to fix the invalid values when reading from XML without XML mapper?

Reply
Contributor
Posts: 34

How to fix the invalid values when reading from XML without XML mapper?

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!

Super User
Super User
Posts: 7,401

Re: How to fix the invalid values when reading from XML without XML mapper?

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.));
Contributor
Posts: 34

Re: How to fix the invalid values when reading from XML without XML mapper?

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

Super User
Super User
Posts: 7,401

Re: How to fix the invalid values when reading from XML without XML mapper?


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.

Contributor
Posts: 34

Re: How to fix the invalid values when reading from XML without XML mapper?

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

 

Super User
Posts: 10,500

Re: How to fix the invalid values when reading from XML without XML mapper?


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.

Ask a Question
Discussion stats
  • 5 replies
  • 55 views
  • 0 likes
  • 3 in conversation