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

I'm trying to import xml-file to SAS. Xml-file is a Google "georesponse" on address request. Here's its fragment:

    <address_component>

      <long_name>1025</long_name>

      <short_name>1025</short_name>

      <type>street_number</type>

    </address_component>

    <address_component>

      <long_name>Gilford Street</long_name>

      <short_name>Gilford St</short_name>

      <type>route</type>

     </address_component>

     <address_component>

      <long_name>West End</long_name>

      <short_name>West End</short_name>

      <type>neighborhood</type>

      <type>political</type>

    </address_component>

    <address_component>

      <long_name>Vancouver</long_name>

      <short_name>Vancouver</short_name>

      <type>locality</type>

      <type>political</type>

    </address_component>

I've attached the full xml-file.

I want to convert it into SAS-dataset like this:

typelong_name

street_number

1025
routeGilford St
neighborhood West End

etc

As you can see, some <address_component>-elements have inside only one <type>-element (like street_number or route), but others have two: first one with value of interest (e.g. 'neighborhood') and the second - with value 'political', which I don't need.

So I created XML-map in XML Mapper, using function POSITION()=1, to ensure that only first occurence of <type>-tag used:

<?xml version="1.0" encoding="windows-1252"?>

<!-- ############################################################ -->

<!-- 2014-01-03T17:07:57 -->

<!-- SAS XML Libname Engine Map -->

<!-- Generated by XML Mapper, 903000.0.0.20110518190000_v930 -->

<!-- ############################################################ -->

<!-- ###  Validation report                                   ### -->

<!-- ############################################################ -->

<!-- XMLMap validation completed successfully. -->

<!-- ############################################################ -->

<SXLEMAP description="Google Georesponse" name="GeocodeResponse" version="2.1">

    <NAMESPACES count="0"/>

    <!-- ############################################################ -->

    <TABLE name="GeoResponse">

        <TABLE-PATH syntax="XPath">/GeocodeResponse/result/address_component</TABLE-PATH>

        <COLUMN name="type">

            <PATH syntax="XPath">/GeocodeResponse/result/address_component/type[position()=1]</PATH>

            <TYPE>character</TYPE>

            <DATATYPE>string</DATATYPE>

            <LENGTH>27</LENGTH>

        </COLUMN>

        <COLUMN name="long_name">

            <PATH syntax="XPath">/GeocodeResponse/result/address_component/long_name</PATH>

            <TYPE>character</TYPE>

            <DATATYPE>string</DATATYPE>

            <LENGTH>17</LENGTH>

        </COLUMN>

    </TABLE>

</SXLEMAP>

And it works properly in XML Mapper itself (in the tab Table View  you can see the expected values). But when I run the code using this map in SAS EG 4.3 or SAS Base 9.3, column 'type' is empty.

If I don't use POSITION()=1 in the map, then everything works well (but for all items except strett_number, route and postal_code I've got 'political' as type, not 'city', 'country' etc).

I use this code for checking:

filename  gilfordl 'R:\gilfordl.xml';

filename  SXLEMAP 'R:\georesponsemap.map';

libname   gilfordl xmlv2 xmlmap=SXLEMAP access=READONLY;

DATA GeoResponse; SET gilfordl.GeoResponse; run;

When tried the same with XML Mapper 9.2 (and verison of SXLEMAP 1.2 ) and SAS 9.2 everything works perfectly. But not with SAS 9.3. For the latter I tried to change version of SXLEMAP to 1.2 in the map-file and to use XML engine instead of XMLV2, but still no luck - type is empty. But as soon as I delete position()=1, everything works.

Does anybody have any clue where can a problem be? Some SAS 9.3 specific?

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

Hi Dmitry

As a workaround you can use the XML engine as you and a map file with version 1.2, this work for me with SAS9.4

View solution in original post

6 REPLIES 6
BrunoMueller
SAS Super FREQ

Hi Dmitry

As a workaround you can use the XML engine as you and a map file with version 1.2, this work for me with SAS9.4

Dmitry
Obsidian | Level 7

Hi Bruno,

that's the case: I tried to do it  - run LIBNAME with XML and XML-map version=1.2 on SAS 9.3, but it didn't work.

Well, it definitely works, not sure why I decided before that it didn't Smiley Happy

BrunoMueller
SAS Super FREQ

Dmitry

I recommend to contact tech support about this

Bruno

BillM_SAS
SAS Employee

I checked and there is an existing defect filed for this issue with the XMLV2 LIBNAME engine. I am adding your posting to the defect information.

I put together a short SAS program that allows you to do what you want with the XMLV2 LIBNAME engine in the latest version of SAS V9.3 (maintenance 2). In the attached SAS program, I create a XML Mapper file within the SAS session using the AUTOMAP option (introduced in SAS 9.3 maintenance 2). Since this is a default XML Mapper file, it does not use the POSITION function. The results from the SAS program appear to match your desired output:

                     Obs    type                           long_name

                      1     street_number                  1025

                      2     route                          Gilford Street

                      3     neighborhood                   West End

                      4     locality                       Vancouver

                      5     administrative_area_level_2    Greater Vancouver

                      6     administrative_area_level_1    British Columbia

                      7     country                        Canada

                      8     postal_code                    V6G 1R2

I did not spend a lot of time on the SAS program, so I suspect that there are better and/or more efficient ways in the SQL procedure code I wrote to get the desired results. I'm not a SQL procedure expert. I just wanted a proof-of-concept to show how to get around the problem you note with the POSITION function and the XMLV2 LIBNAME engine.

Dmitry
Obsidian | Level 7

Thanks, BillM@SAS.

I'm sure it works, but I can't use it, I'm receiving error message about wrong option in LIBNAME statement. My SAS version is  9.3 (TS1M0), probably that's why, since you said that AUTOMAP was introduced in SAS 9.3 maintenance 2.

BillM_SAS
SAS Employee

The AUTOMAP option just saved me the step of running the XML Mapper application, creating a default XML Mapper file, and saving the XML Mapper file. Since you are not running SAS v9.3 maintenance 2, just create the XML Mapper file manually with the "Automatically generate and XMLMap from the XML structure" button on the toolbar in the XML Mapper application (see attached image). Once the XML Mapper file is created and saved and the LIBNAME statement adjusted to use your created XML Mapper file, the rest of the code should run fine.


automappingButton.png

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, 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
  • 6 replies
  • 2424 views
  • 4 likes
  • 3 in conversation