topic Re: How to fix the invalid values when reading from XML without XML mapper? in SAS Programming

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

jjames1 — Tue, 01 Aug 2017 14:08:50 GMT

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!

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

RW9 — Tue, 01 Aug 2017 14:23:43 GMT

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

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

jjames1 — Tue, 01 Aug 2017 14:29:41 GMT

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

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

RW9 — Tue, 01 Aug 2017 14:39:21 GMT

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

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

jjames1 — Tue, 01 Aug 2017 14:44:11 GMT

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

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

ballardw — Tue, 01 Aug 2017 15:11:17 GMT

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