topic Import XML file into SAS in SAS Procedures

Import XML file into SAS

krish_saptak — Thu, 11 Nov 2010 12:11:16 GMT

I have .xml file like below. I want to Import to SAS and create SAS dataset from it.

-
-
1
1
2
B
534941
Section-1
44624
0.4462
1
1
2

-
2
1
2
B
534941
Section-1
46604
0.466
2
2
6

-
3
1
2
B
534941
Section-1
10264
0.1026
1
1
3

-
4
1
1
A
534941
Section-1
93289
0.9329
2
2
6

-
5
1
1
A
534941
Section-1
43915
0.4391
1
1
3

-
6
1
2
B
534941
Section-1
32549
0.3255
2
2
6

-
7
1
1
A
534941
Section-1
80389
0.8039
1
1
3

-
8
1
1
A
534941
Section-1
99830
0.9983
1
1
1

-
9
1
2
B
534941
Section-1
11299
0.113
2
2
4

-
10
1
2
B
534941
Section-1
35463
0.3546
1
1
1

-
11
1
1
A
534941
Section-1
65725
0.6573
2
2
5

-
12
1
1
A
534941
Section-1
63333
0.6333
1
1
2

-
13
1
1
A
534941
Section-1
38953
0.3895
2
2
5

-
14
1
2
B
534941
Section-1
34351
0.3435
1
1
2

-
15
1
2
B
534941
Section-1
63197
0.632
2
2
5

-
16
1
1
A
534941
Section-1
86315
0.8632
1
1
2

-
17
1
2
B
534941
Section-1
16371
0.1637
2
2
5

-
18
1
1
A
534941
Section-1
5537
0.0554
1
1
2

-
19
1
1
A
534941
Section-1
73672
0.7367
2
2
6

-
20
1
1
A
534941
Section-1
8159
0.0816
1
1
3

-
21
1
1
A
534941
Section-1
71549
0.7155
2
2
6

-
22
1
2
B
534941
Section-1
50638
0.5064
1
1
3

-
23
1
2
B
534941
Section-1
70114
0.7011
2
2
6

-
24
1
1
A
534941
Section-1
46666
0.4667
2
2
4

-
25
1
2
B
534941
Section-1
97431
0.9743
1
1
1

-
26
1
2
B
534941
Section-1
48908
0.4891
2
2
4

-
27
1
2
B
534941
Section-1
46351
0.4635
1
1
1

-
28
1
1
A
534941
Section-1
38919
0.3892
2
2
5

can anyone help.

I tried like this.
libname myxml xml 'C:\kk\randlist.xml';\

DATA randlist;
SET myxml.randlist;
RUN;

But it shows error in log htat myxml.randlist.data doesnot exist.

Re: Import XML file into SAS

Peter_C — Thu, 11 Nov 2010 12:32:28 GMT

try it with the xml-mapper
that should be installed with SAS9
The version installed with SAS9.2 provides an "auto gen" tool.
This revealed only one column in table RandList and 13 columns in a table called Simulation_1
I did have to clean out those - near the beginning of the xml ( before RandList and Simulation-1).
Were they supposed to convey something?

peterC

Re: Import XML file into SAS

Cynthia_sas — Thu, 11 Nov 2010 17:12:21 GMT

Hi:
The SAS XML Libname Engine expects a -VERY- flat non-hierarchical XML file with 1 and ONLY 1 root tag. <RandList> is your root tag and <Simulation-1> looks like it delimits each observation. The problem is that either you have to use the XML Mapper to write a map to deal with the invalid name 'Simulation-1' or you have to alter the XML. If you've never used an XML Map, you may want to ask the folks who generated the XML to just take the hyphen out of the <Simulation-1> tag.

This syntax worked for me, but I had to use Notepad to edit the file and change the <Simulation-1> tag to be <Simulation1> without the '-' in the tag. See partial PROC PRINT output below.

cynthia

The program:
[pre]
libname myxml xml 'C:\temp\randlist.xml';

DATA randlist;
SET myxml.simulation1;
RUN;

ods listing;
proc contents data=work.randlist;
run;

proc print data=work.randlist;
run;

libname myxml clear;
[/pre]

Partial modified RANDLIST.XML:
[pre]
<?xml version="1.0" standalone="yes" ?>
<RandList>
<Simulation1>
<Sequence>1</Sequence>
<SectionId>1</SectionId>
<TreatmentGroupId>2</TreatmentGroupId>
<TreatmentGroup>B</TreatmentGroup>
<SeedValue>534941</SeedValue>
<SectionName>Section-1</SectionName>
<Code>44624</Code>
<ProbablityValue>0.4462</ProbablityValue>
<TB>1</TB>
<PT>1</PT>
<site>2</site>
</Simulation1>
<Simulation1>
<Sequence>2</Sequence>
<SectionId>1</SectionId>
<TreatmentGroupId>2</TreatmentGroupId>
<TreatmentGroup>B</TreatmentGroup>
<SeedValue>534941</SeedValue>
<SectionName>Section-1</SectionName>
<Code>46604</Code>
<ProbablityValue>0.466</ProbablityValue>
<TB>2</TB>
<PT>2</PT>
<site>6</site>
</Simulation1>
. . . more XML . . .
</RandList>
[/pre]

Partial output PROC PRINT:
[pre]
Obs SITE PT TB PROBABLITYVALUE CODE SECTIONNAME SEEDVALUE TREATMENTGROUP TREATMENTGROUPID SECTIONID SEQUENCE

1 2 1 1 0.4462 44624 Section-1 534941 B 2 1 1
2 6 2 2 0.466 46604 Section-1 534941 B 2 1 2
3 3 1 1 0.1026 10264 Section-1 534941 B 2 1 3
4 6 2 2 0.9329 93289 Section-1 534941 A 1 1 4
5 3 1 1 0.4391 43915 Section-1 534941 A 1 1 5
[/pre]

I didn't show the PROC CONTENTS to save space.