topic Re: Convertin multiple XML columns into a single SAS data column in New SAS User

Convertin multiple XML columns into a single SAS data column

crowtrobot — Fri, 28 Sep 2018 20:23:58 GMT

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?

Re: Convertin multiple XML columns into a single SAS data column

mkeintz — Sat, 29 Sep 2018 04:48:56 GMT

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.

Re: Convertin multiple XML columns into a single SAS data column

Vince_SAS — Sun, 30 Sep 2018 20:07:52 GMT

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&docsetVersion=9.4

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