BookmarkSubscribeRSS Feed
crowtrobot
Calcite | Level 5

I have a data set in an XML file with a format similar to the sample below.

 

<?xml version="1.0" encoding="UTF-8"?>
<PEOPLE xmlns:HOME="http://sample.url.org/home"
xmlns:IP="http://sample.url.org/ip"
xmlns:WORK="http://sample.url.org/work">
<PERSON>
<NAME>Joe Smith</NAME>
<HOME:ADDRESS>1234 Elm Street</HOME:ADDRESS>
<HOME:PHONE>999-555-0011</HOME:PHONE>
<WORK:ADDRESS>2001 Office Drive, Box 101</WORK:ADDRESS>
<WORK:PHONE>999-555-0101</WORK:PHONE>
<IP:ADDRESS>192.168.1.1</IP:ADDRESS>
</PERSON>

<PERSON>
<NAME>Pat Perkinson</NAME>
<HOME:ADDRESS>1395 Half Way</HOME:ADDRESS>
<HOME:PHONE>999-555-0033</HOME:PHONE>
<WORK:ADDRESS>2001 Office Drive, Box 103</WORK:ADDRESS>
<WORK:PHONE>999-555-0103</WORK:PHONE>
<IP:ADDRESS>10.0.1.1</IP:ADDRESS>
</PERSO

 

 

 I am looking to create a SAS data set where the Name is stored in one column, the Home Address and Phone number are stored together in a single column and the Work Address and Phone are stored together in a single column. I have an output XML mapping document that I used to create the XML data set in the first place. Is there any way to modify that to read the data back into SQL as combined columns?

2 REPLIES 2
mkeintz
PROC Star

I've never needed to import XML, so I have no experience to base the following comment on.  But if it is easier to import the addresses and phone numbers as separate columns (separate variables in a SAS data set), I'd strongly recommend doing so.  Then you can read that sas dataset in a data step program where you could easily implement combining of addresses with corresponding phone numbers.

 

So first see if you can read the XML as it is currently structured.  I.e. examine the resulting SAS data set to determine what variables have been created.  If that works, you can redefine your question to ask how to combine variables in a data step.  If it doesn't then simplify your question to one focused on making XML import working.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Vince_SAS
Rhodochrosite | Level 12

It looks like this documentation page will help you:

 

Including Namespace Elements in an XMLMap

https://go.documentation.sas.com/?docsetId=engxml&docsetTarget=p1l4r1tyrnsapdn1gcdrf9t00c8m.htm&docs...

 

Try this code to see if it gives you what you need:

 

filename indata 'C:\temp\forum.xml';

*  Create the input XML file;

data _null_;
infile cards4;
file indata;
input;
put _infile_;
cards4;
<?xml version="1.0" encoding="UTF-8"?>
<PEOPLE xmlns:HOME="http://sample.url.org/home" 
   xmlns:IP="http://sample.url.org/ip" 
   xmlns:WORK="http://sample.url.org/work">
    <PERSON>
        <NAME>Joe Smith</NAME>
        <HOME:ADDRESS>1234 Elm Street</HOME:ADDRESS>
        <HOME:PHONE>999-555-0011</HOME:PHONE>
        <WORK:ADDRESS>2001 Office Drive, Box 101</WORK:ADDRESS>
        <WORK:PHONE>999-555-0101</WORK:PHONE>
        <IP:ADDRESS>192.168.1.1</IP:ADDRESS>
    </PERSON>
    <PERSON>
        <NAME>Jane Jones</NAME>
        <HOME:ADDRESS>9876 Main Street</HOME:ADDRESS>
        <HOME:PHONE>999-555-0022</HOME:PHONE>
        <WORK:ADDRESS>2001 Office Drive, Box 102</WORK:ADDRESS>
        <WORK:PHONE>999-555-0102</WORK:PHONE>
        <IP:ADDRESS>172.16.1.2</IP:ADDRESS>
    </PERSON>
    <PERSON>
        <NAME>Pat Perkinson</NAME>
        <HOME:ADDRESS>1395 Half Way</HOME:ADDRESS>
        <HOME:PHONE>999-555-0033</HOME:PHONE>
        <WORK:ADDRESS>2001 Office Drive, Box 103</WORK:ADDRESS>
        <WORK:PHONE>999-555-0103</WORK:PHONE>
        <IP:ADDRESS>10.0.1.3</IP:ADDRESS>
    </PERSON>
</PEOPLE>
;;;;
run;

*  Create the XML map;

filename map 'C:\temp\forum.map';

data _null_;
infile cards4;
file map;
input;
put _infile_;
cards4;
<SXLEMAP name="Namespace" version="2.1"> 
    <NAMESPACES count="3">
        <NS id="1" prefix="HOME">http://sample.url.org/home</NS> <!--2-->
        <NS id="2" prefix="IP">http://sample.url.org/ip</NS> 
        <NS id="3" prefix="WORK">http://sample.url.org/work</NS>
    </NAMESPACES>
    <TABLE description="PERSON" name="PERSON">
        <TABLE-PATH syntax="XPath">/PEOPLE/PERSON</TABLE-PATH>
        <COLUMN name="NAME">
            <PATH syntax="XPath">/PEOPLE/PERSON/NAME</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>13</LENGTH>
        </COLUMN>
        <COLUMN name="ADDRESS">
            <PATH syntax="XPathENR">/PEOPLE/PERSON/{1}ADDRESS</PATH> <!--5-->
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>16</LENGTH>
        </COLUMN>
        <COLUMN name="PHONE">
            <PATH syntax="XPathENR">/PEOPLE/PERSON/{1}PHONE</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>12</LENGTH>
        </COLUMN>
        <COLUMN name="ADDRESS1">
            <PATH syntax="XPathENR">/PEOPLE/PERSON/{3}ADDRESS</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>26</LENGTH>
        </COLUMN>
        <COLUMN name="PHONE1">
            <PATH syntax="XPathENR">/PEOPLE/PERSON/{3}PHONE</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>12</LENGTH>
        </COLUMN>
        <COLUMN name="ADDRESS2">
            <PATH syntax="XPathENR">/PEOPLE/PERSON/{2}ADDRESS</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>11</LENGTH>
        </COLUMN>
    </TABLE>
</SXLEMAP>
;;;;
run;

*  Access the data using PROC SQL;

libname indata xmlv2 xmlmap=map;

proc sql;
  create table work.person as
  select name,
         catx(' | ', address, phone)   as home_info,
         catx(' | ', address1, phone1) as work_info,
         address2 as IP
   from indata.person;
quit;

proc print data=work.person; run; quit;

 

Vince DelGobbo

SAS R&D

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 669 views
  • 0 likes
  • 3 in conversation