DATA Step, Macro, Functions and more

Import only Delivered columns using XMLMAP

Accepted Solution Solved
Reply
Occasional Contributor ADN
Occasional Contributor
Posts: 6
Accepted Solution

Import only Delivered columns using XMLMAP

Hi,

 

I am using SAS XMLMAP to import xml files. My question is if I have 10 columns defined in the xmlmap file and suppose the xml file contains only 5 of them, all the 10 columns get imported into SAS(The 5 not present gets imported as blanks).

 

Is there any option to import only the subset of the columns present in the xml?


Accepted Solutions
Solution
‎04-19-2017 08:56 AM
Super User
Super User
Posts: 7,401

Re: Import only Delivered columns using XMLMAP

So the process is correct then.  The dataset reads up to the full amount of variables, with missing data being missing.  Now after that part, what you do with the data is a different matter, you may want to drop the blank columns, in which case a datastep with a list for keep= is the simplest.  To be honest though, if it was me writing code and I expected 10 fields to come in and some didn't, I would just carry on with programming for 10 columns, and the missing information wouldn't do anything, otherwise you will get to a situation where your program needs to be changed each time there is varying amounts of columns.  Far bettter to have a fixed structure.

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: Import only Delivered columns using XMLMAP

This i smore of a process issue.  What does your import specification say?  You do have one of those don't you - otherwise, well you could have anything come in.  An agreement needs to be in place between sender and recipient otherwise its just guess work.  Does the XML have a DTD with it?  As far as I know you can only import what is in the XMLMAP file, but you can drop columns if they are blank afterwards (there several examples of that on this forum), but the real question is why do you expect xyz columns but are not getting them?

Occasional Contributor ADN
Occasional Contributor
Posts: 6

Re: Import only Delivered columns using XMLMAP

The issue I have is that in totality the sender is supposed to send 10 columns. But they don't send all the 10 columns at once. They send it incrementally. So when he sends me 5 columns, I only want the 5 columns to popup in my table...

Solution
‎04-19-2017 08:56 AM
Super User
Super User
Posts: 7,401

Re: Import only Delivered columns using XMLMAP

So the process is correct then.  The dataset reads up to the full amount of variables, with missing data being missing.  Now after that part, what you do with the data is a different matter, you may want to drop the blank columns, in which case a datastep with a list for keep= is the simplest.  To be honest though, if it was me writing code and I expected 10 fields to come in and some didn't, I would just carry on with programming for 10 columns, and the missing information wouldn't do anything, otherwise you will get to a situation where your program needs to be changed each time there is varying amounts of columns.  Far bettter to have a fixed structure.

Occasional Contributor ADN
Occasional Contributor
Posts: 6

Re: Import only Delivered columns using XMLMAP

Yes. As such it is not much of an issue. But I cannot differentiate between these two cases:

 

  • The sender sent a column but it is blank altogether
  • The sender didn't send a column at all

 

In the first case, I have to report it as a defect while in the second case I don't do anything.....

Super User
Super User
Posts: 7,401

Re: Import only Delivered columns using XMLMAP

TBHI don't know of a way of checking missing in the XML from the XMLmapper.  If it was me I would pre-scan the file in a datastep infile to pull out the structure of the XML and do the checks on that.

Super User
Super User
Posts: 6,499

Re: Import only Delivered columns using XMLMAP

You need to make another XMLMAP (or another table in the same map?) where the name of the column is the DATA to be extracted instead of the metadata to be used to name the column that contains the data.  

 

Then you can extract that new metadata table and query it to see what columns where actually contained in the XML file.

Occasional Contributor ADN
Occasional Contributor
Posts: 6

Re: Import only Delivered columns using XMLMAP

Hi, Can you elaborate? Not entirely clear to me....
Super User
Super User
Posts: 6,499

Re: Import only Delivered columns using XMLMAP

It really depends on how the XML is constructed. If you are luck there is an actual separate table at the top of the XML file that lists the names of the columns included that will be easy to import. Otherwise you could see if it is possible in the XML map to read the value of a path in the XML into a variable.

 

If all else fails just read the XML as a text file and parse it yourself using a data step.

 

If you can post the text of a simple version of your XML file structure with just two or three variables and two or three observations then perhaps someone here can help.

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 105 views
  • 2 likes
  • 3 in conversation