How do I create a xml file, which can be imported by SAS and can also be edited and saved by excel?

[Ethan1]

Dear,

Is there a way to create an xml file, which can be imported by sas  (without a map file) and can also be edited and saved using excel software?

I tried to use excel to save as xml file, but it can not be imported into sas due to the lack of map file(In fact, map files cannot be used in this requirement).

At present, the solution I think of is to save a xls/xlsx file as an xml file through excel software, and then create a macro to translate the xml file to a sas dataset. In this way, I can open this xml for editing and saving, and also import it into SAS dataset with my macro.

Is there a better solution?

[Reeza]

---

@Ethan1 wrote:

I tried to use excel to save as xml file, but it can not be imported into sas due to the lack of map file(In fact, map files cannot be used in this requirement).

---

How did you try and import the xml file? 

[Ethan1]

Thank you for your reply！

1. open the XLSX file with excel software

2. save it as XML spreadsheet 2003(*.xml)

3. SAS CODE: libname xxx xml ".../xxx.xml";

If I open this xml file with Notpad, it's not a 'GENERIC' structure XML. So I can't import it directly with sas without using a map file. 

<Row>
<Cell><Data ss:Type="String">TSIDEM01_IA</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">TITLE</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">Summary of Demographics and Baseline Characteristics – Open Session; Safety Analysis Set </Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
</Row>

Finally, the method I used is to directly transfer the above plain text into a sas data set by regular expressions . 

[Tom]

How did you get Excel to create an XML file? The only option I see is to create something called an XML Spreadsheet 2003 format XML file.  If you are going to save it as a spreadsheet just leave it as an XLSX file.

[Ethan1]

Thanks Tom! 

The question is that, in this requirement, we cannot use xlsx/xls file. 

We need to meet the following requirements at the same time

1. This file can be edited and saved by OFFICE-WORD/EXCEL and the file type cannot be xls/xlsx(It's really weird！)

2. This file can be imported into sas dataset.

[Reeza]

CSV?

[Tom · Accepted solution]

So is the question how to read one of those XML 2003 Spreadsheet files?

Does this help?

I made a simple worksheet in Excel and saved it as that XML style.

Now I can read it with SAS.

filename xmlwb "c:\downloads\from_excel.xml";
filename map temp;
libname xmlwb xmlv2 automap=reuse xmlmap=map;

proc sql ;
create table want as 
select * 
from xmlwb.workbook
natural join xmlwb.worksheet 
natural join xmlwb.table
natural join xmlwb.row
natural join xmlwb.cell
natural join xmlwb.data
order by 1
;
quit;
proc print width=min data=want;
run;

                                                                T
                                                                a
                                                                b
                                                                l    T                T
                                                                e    a                a
                                                                _    b                b
                                                                E    l                l
                                                                x    e                e
                    W                                           p    _    T           _
                    o    W                                      a    E    a           D
                    r    o                                      n    x    b           e
                    k    r                              W       d    p    l    T      f
               T    s    k                              o       e    a    e    a      a
     C         a    h    b    D                         r       d    n    _    b      u
     e    R    b    e    o    a                         k       C    d    F    l      l
     l    o    l    e    o    t                         s       o    e    u    e      t
     l    w    e    t    k    a      D                  h       l    d    l    _      R
     _    _    _    _    _    _      a                  e       u    R    l    F      o
     O    O    O    O    O    O      t                  e       m    o    C    u      w
     R    R    R    R    R    R      a                  t       n    w    o    l      H
     D    D    D    D    D    D      _                  _       C    C    l    l      e
     I    I    I    I    I    I      T         D        N       o    o    u    R      i
O    N    N    N    N    N    N      y         a        a       u    u    m    o      g
b    A    A    A    A    A    A      p         t        m       n    n    n    w      h
s    L    L    L    L    L    L      e         a        e       t    t    s    s      t

1    1    1    1    1    1    1    String    X        Sheet1    3    2    1    1    14.4
2    2    1    1    1    1    2    String    Y        Sheet1    3    2    1    1    14.4
3    3    1    1    1    1    3    String    Z        Sheet1    3    2    1    1    14.4
4    4    2    1    1    1    4    Number    1        Sheet1    3    2    1    1    14.4
5    5    2    1    1    1    5    Number    2        Sheet1    3    2    1    1    14.4
6    6    2    1    1    1    6    String    hello    Sheet1    3    2    1    1    14.4