topic Re: POSITION()-function in PATH-element of XML-map doesn't work in SAS 9.3 in SAS Programming

POSITION()-function in PATH-element of XML-map doesn't work in SAS 9.3

Dmitry — Mon, 06 Jan 2014 17:57:18 GMT

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:

type	long_name
street_number	1025
route	Gilford 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"?>

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

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

<TYPE>character</TYPE>

<DATATYPE>string</DATATYPE>

</COLUMN>

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

<TYPE>character</TYPE>

<DATATYPE>string</DATATYPE>

</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?

Re: POSITION()-function in PATH-element of XML-map doesn't work in SAS 9.3

BrunoMueller — Tue, 07 Jan 2014 08:42:53 GMT

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

Re: POSITION()-function in PATH-element of XML-map doesn't work in SAS 9.3

Dmitry — Tue, 07 Jan 2014 21:14:30 GMT

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

Re: POSITION()-function in PATH-element of XML-map doesn't work in SAS 9.3

BrunoMueller — Wed, 08 Jan 2014 07:23:35 GMT

Dmitry

I recommend to contact tech support about this

Bruno

Re: POSITION()-function in PATH-element of XML-map doesn't work in SAS 9.3

BillM_SAS — Thu, 09 Jan 2014 16:05:00 GMT

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.

Re: POSITION()-function in PATH-element of XML-map doesn't work in SAS 9.3

Dmitry — Thu, 09 Jan 2014 17:56:38 GMT

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.

Re: POSITION()-function in PATH-element of XML-map doesn't work in SAS 9.3

BillM_SAS — Thu, 09 Jan 2014 18:11:19 GMT

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.