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;
... View more
