Hello,
I would like to import a xml file. Therefore I use the SAS XML- Mapper to create a map.
I was able to retrieve data with direct Xpath. Like : “/records/record/Data/code” in the example below.
But I have a problem with complexe path (XPATH containing queries) : “/records/record/Data/action[type="TYPE_A"]/coefficient”
----------------------------------xml file data.xml----------------------------------
<?xml version="1.0" encoding="utf-8"?>
<records>
<record>
<Data>
<code>AAA00001</code>
<action>
<type>TYPE_A</type>
<coefficient>0.75</coefficient>
</action>
<action>
<type>TYPE_B</type>
<coefficient>1</coefficient>
</action>
<coverage>
<isSelected>true</isSelected>
<type>OPTIONAL</type>
<deductible>
<value>1.5</value>
</deductible>
<label>Label_1</label>
<identifier>ID_001</identifier>
</coverage>
</Data>
</record>
</records>
------------------------------------------------------------------------------
----------------------------------map file data.map----------------------------------
<?xml version="1.0" encoding="windows-1252"?>
<SXLEMAP xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="nameTest" version="1.2" xsi:noNamespaceSchemaLocation="http://www.sas.com/xml/schema/sxle12.xsd">
<TABLE name="DataTable">
<TABLE-PATH syntax="XPath">/records/record</TABLE-PATH>
<COLUMN name="code">
<PATH syntax="XPath">/records/record/Data/code</PATH>
<LABEL>code</LABEL>
<TYPE>character</TYPE>
<DATATYPE>STRING</DATATYPE>
<LENGTH>16</LENGTH>
</COLUMN>
<COLUMN name="TYPE_A">
<PATH syntax="XPath">/records/record/Data/action[type="TYPE_A"]/coefficient</PATH>
<LABEL>Type A</LABEL>
<TYPE>numeric</TYPE>
<DATATYPE>double</DATATYPE>
</COLUMN>
<COLUMN name="TYPE_B">
<PATH syntax="XPath">/records/record/Data/action[type="TYPE_B"]/coefficient</PATH>
<LABEL>Type B</LABEL>
<TYPE>numeric</TYPE>
<DATATYPE>double</DATATYPE>
</COLUMN>
<COLUMN name="OPTION">
<PATH syntax="XPath">/records/record/Data/coverage[isSelected="true"][type="OPTIONAL"]/label</PATH>
<LABEL>OPTIONAL label</LABEL>
<TYPE>character</TYPE>
<DATATYPE>STRING</DATATYPE>
<LENGTH>250</LENGTH>
</COLUMN>
<COLUMN name="deductible">
<PATH syntax="XPath">/records/record/Data/coverage[(isSelected="true") and (identifier="ID_001" or identifier="ID_002")]/deductible/value</PATH>
<LABEL>deductible value</LABEL>
<TYPE>numeric</TYPE>
<DATATYPE>double</DATATYPE>
</COLUMN>
</TABLE>
</SXLEMAP>
------------------------------------------------------------------------------
I want to convert it into SAS table like this:
Code TYPE_A TYPE_B OPTION deductible
AAA00001 0.75 1 Label_1 1.5
Is there any possiblity to get this done either directly in the XML-Mapper or as an workaround?
Thanks in advance!
I can't figure this our with the XML mapper, but I can code for it:
libname x xmlv2 "c:/temp/mtajouri.xml" automap=REPLACE xmlmap="c:/temp/mtajouri.map";
proc sql;
create table temp1 as
select distinct
a.data_ordinal
,code
,a.type
,coefficient as Type_Value
from x.Action a
,x.Coverage c
,x.Data
where a.Data_ordinal=c.Data_ordinal
and a.Data_ordinal=data.Data_ordinal
order by Code, a.data_ordinal
;
create table t2 as
select code
,label as Option
from x.Coverage c
,x.Data
where c.Data_ordinal=data.Data_ordinal
order by Code, c.data_ordinal
;
create table t3 as
select code
,value as Deductible
from x.coverage c
,x.Data
,x.Deductible d
where data.Data_ordinal=c.Data_ordinal
and c.Coverage_ordinal=d.coverage_ordinal
order by Code
;
quit;
libname x clear;
proc transpose data=temp1 out=t1(drop=_:) ;
by code;
id type;
var Type_Value;
run;
proc sql;
create table want as
select t1.Code
,TYPE_A
,TYPE_B
,OPTION
,Deductible
from t1 inner join t2
on t1.code=t2.code
inner join t3
on t1.code =t3.code
order by t1.code
;
drop table temp1;
drop table t1;
drop table t2;
drop table t3;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.