BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ADN
Fluorite | Level 6 ADN
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

ADN
Fluorite | Level 6 ADN
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ADN
Fluorite | Level 6 ADN
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Tom
Super User Tom
Super User

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.

ADN
Fluorite | Level 6 ADN
Fluorite | Level 6
Hi, Can you elaborate? Not entirely clear to me....
Tom
Super User Tom
Super User

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.

 

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
  • 8 replies
  • 1038 views
  • 2 likes
  • 3 in conversation