BookmarkSubscribeRSS Feed
saskapa
Quartz | Level 8

Hi,

I have issues to convert a sas dataset into an xml file  with proc template  that must have a particular structure

The starting point is my sas dataset :

data test;

input order date name $ age;

datalines;

1 20150701 jim  45

2 20150705 jon  40

2 20150705 kevin 32

3 20150708 bill  60

3 20150708 brian 51

3 20150708 neil  36

;

run;

I would like to convert this sas file into an hierarchical xml file that have to be like this :

==>  just simply run the code here below.

data _null_;

    set test end=last;

  by order;

file  'C:\test.xml';

if _n_=1 then      put '<root>';

if first.order then do ;

                    put ' <snap>';

  put '  <order>'order'</order>';

  put '  <date>' date'</date>';

end;

put '  <detail>';

put '    <name>' name'</name>';

put '    <age>' age'</age>';

put '  </detail>';

if last.order then do ;

                    put ' </snap>';

end;

if last then put '</root>';

run;

However I have been asked to avoid this coding and achieve it by using proc template;

Detail about my structure:

As you can see my code create a by-group node called <snap> for  each distinct  value of variable 'order'.

I create 3  node called

<order>

<date>

<detail>

<detail> and <snap> are not from the dataset. For each node called <detail> I then trigger the value of variable name and age ( as long as I am within the by-group) ;

Base on the article of Cynthia L. Zender  " Creating a Tagset Template for SAS XML Libname Engine" I have  tried generate with proc template

the same by-group hierarchical xml file structure.

In order to do so, I followed her example on page 15 which consist in

1. Create two macro variable in a data step to allow by-group processing in proc template

data rik_1;

  set test;

  by order ;

  drop count;

    if first.order  then do;

    count=0;

    call symput ('firstsnap',"yes");

    call symput ('lastsnap',"no");

  *coding 1 if firstsnap=yes;

    a=1;

    end;

count+1;

  if count gt 1 then  do ;

  call symput ('firstsnap',"no");

  *coding 1 if firstsnap=no;

  a=0;

  end;

  if last.order  then do ;

  call symput ('lastsnap',"yes");

  b=1;

end;

run;

2. Add in proc template the following statement:

  • Call macro variable

        mvar firstsnap lastsnap;

  • Define a putby event that will be triggered if  by-group specific condition is met

      define event putby ;

        start:

        put "<snap>" NL;

        ndent;

        set $name  "order";    trigger EmitCol ;

        set $name  "date";    trigger EmitCol ;

        ndent;

        break;

      finish:

      put "</sanp>" NL ;

      xdent;

      break;

      end;

And by using the macro variables for creating by-group tags :

define event row;

      start:

        break / if !cmp(SECTION, "body");

        break / if cmp(XMLMETADATA, "ONLY");

        eval    $index 1;

        unset  $col_values;

        break;

      finish:

        break / if !cmp(SECTION, "body");

        break / if cmp(XMLMETADATA, "ONLY");

        ndent;

  /* condition is : if first.order is true then firstsnap=yes and start section of event putby will be triggered*/

        trigger putby start /if cmp(firstsnap,"yes");

/* here Emitrow is triggered*/

        trigger EmitRow ;

        xdent;

/* condition is : if last.order is true then firstsnap=no and finish section of event putby will be triggered*/

      trigger putby finish /if cmp(lastsnap,"yes");

        break;

      end;

With those definition I would be be able to create tags in the same way as I've done above.:

I provide also in below the proc template code.

libname fin xml 'C:\rike.xml' tagset=tagsets.rik;

data fin.root; set rik_1;

run;

Unfortunately there are few issues :

1. tag <snap> ,<order> and <date> are not triggered at all

2. tag </snap>  is triggered for every observation but it should only be triggered at the end of each by group variable ( here order).

It is really strange because the macro variable are defined correctly and the conditional triggering should occur...

Any clue or guidance is welcome

Regards,

Sas_kap

**********************************************************************************************************************************************************************************************************

proc template;

  /* +------------------------------------------------+

      |                                                |

      +------------------------------------------------+ */

  define tagset tagsets.rik ;

      notes "SAS XML Engine output event model(interface)";

      default_event = "basic";

      indent = 1;

      mapsub =%nrstr("/&lt;/&gt;/&amp;/&quot;/&apos;/");

      map = %nrstr("<>&""'");

  mvar firstsnap lastsnap;

  /* +------------------------------------------------+

      |                                                |

      +------------------------------------------------+ */

      define event XMLversion;

        put  '<?xml version="1.0"';

        putq ' encoding=' ENCODING;

        put  ' ?>' CR;

        break;

      end;

      define event XMLcomment;

        put  '<!-- ' CR;

        put  '    ' TEXT CR;

        put  '  -->' CR;

        break;

      end;

      define event initialize;

      set  $LIBRARYNAME          'LIBRARY' ;

      set  $TABLENAME            'ROOT' ;

      set  $COLTAG              'column' ;

      set  $META                'FULL' ;

      eval  $is_engine            1;

      eval  $is_procprint        0;

      eval  $is_OUTBOARD          1;

      end;

  /* +------------------------------------------------+

      |                                                |

      +------------------------------------------------+ */

      define event doc;

      start:

        trigger initialize;

        trigger XMLversion;

        break;

      finish:

        break;

      end;

   

      define event doc_head;

      start:

        break;

      finish:

        break;

      end;

      define event doc_body;

      start:

        break;

      finish:

        break;

      end; 

      define event proc;

      start:

        break / if frame_name ;              /* set by ODS statement use  */

        eval $is_OUTBOARD 0 ;                /* default for non-engine    */

        do / if cmp(XMLCONTROL, "OUTBOARD"); /* only the engine sets this */

            eval $is_OUTBOARD 1 ;

        else ;

            eval $is_OUTBOARD 0 ;

        done ;

        break;

      finish:

        break;

      end;

      define event leaf;

      start:

        /*

          *  PROC PRINT

          *  data set reference is in the value and label fields

          *  and NOT in the output_label field

          */

        eval $is_engine    0; /* NOT ENGINE */

        break / if ^cmp("Print", name);

        eval $is_procprint  1; /* PROC PRINT */

        eval $regex prxparse("/\.(.+)/");

        eval $match prxmatch($regex, value);

        set  $TABLENAME prxposn($regex, 1, value);

        break;

      finish:

        break;

      end;

      define event output;

      start:

        break / if $is_procprint ;

        eval  $is_engine    0;            /* NOT ENGINE  */

        set  $TABLENAME name/ if name;  /* TABLE VIEWER */

        break;

      finish:

        break;

      end;

      define event table;

      start:

        unset $col_names;

        unset $col_types;

        unset $col_width;

        eval  $index      1;

        eval  $index_max  0;

        set  $TABLENAME name / if name;          /* LIBNAME ENGINE */

        set  $META XMLMETADATA / if XMLMETADATA ; /* LIBNAME ENGINE */

        set  $SCHEMA XMLSCHEMA / if XMLSCHEMA ;  /* LIBNAME ENGINE */

        break;

      finish:

        break;

      end;

      define event colspecs;

      start:

        break / if cmp(XMLMETADATA, "NONE");

      finish:

        break / if cmp(XMLMETADATA, "NONE");

      end;

      define event colgroup;

      start:

        break / if cmp(XMLMETADATA, "NONE");

      finish:

        break / if cmp(XMLMETADATA, "NONE");

      end;

  /* +------------------------------------------------+

      |                                                |

      +------------------------------------------------+ */

      define event colspec_entry;

      start:

        break / if ^$is_engine and $index eq 1 and cmp(name, "Obs");

        eval  $index_max $index_max+1;

        set $col_names[] name;

        set $col_types[] type;

        set $col_width[] width;

        break;

      finish:

        break;

      end;

      define event table_head;

      start:

        break;

      finish:

        break;

      end;

      define event table_body;

      start:

        trigger EmitMeta ;

        break;

      finish:

        trigger EmitMeta ;

        break;

      end;

  /* +------------------------------------------------+

      |                                                |

      +------------------------------------------------+ */

      define event row;

      start:

        break / if !cmp(SECTION, "body");

        break / if cmp(XMLMETADATA, "ONLY");

        /*eval    $index 1;

        unset  $col_values;*/

        break;

      finish:

        break / if !cmp(SECTION, "body");

        break / if cmp(XMLMETADATA, "ONLY");

  ndent;

        trigger putby start /if cmp(firstsnap,"yes");

  trigger EmitRow ;

  xdent;

  trigger putby finish /if cmp(lastsnap,"yes");

        break;

      end;

      define event data;

      start:

        break / if !cmp(SECTION, "body");

        do / if $is_engine ;

            break / if !cmp(XMLCONTROL, "Data");

        else ;

            break / if !cmp(HTMLCLASS, "Data");

        done ;

        break / if cmp(XMLMETADATA, "ONLY");

        set $name $col_names[$index];

        do / if exists(MISSING);

            eval  $is_MISSING  1;

            eval  $value_MISSING MISSING;

            set  $col_values[$name] " ";

        else ;

            eval  $is_MISSING  0;

            set  $col_values[$name] VALUE;

        done;

        break;

      finish:

        break / if !cmp(SECTION, "body");

        do / if $is_engine ;

            break / if !cmp(XMLCONTROL, "Data");

        else ;

            break / if !cmp(HTMLCLASS, "Data");

        done ;

        break / if cmp(XMLMETADATA, "ONLY");

        set  $name  $col_names[$index];

        eval $index $index+1;

        break;

      end;

  /* +----------------------------------------------------------------------+

      |                                                                      |

      | at this point, we just take over XML output.      |

      | EmitRow() is triggered each time the data is    |

      |          loaded into the $col_values array      .  |

      |                                                                      |

      | we can output anything we desire from here...  |

      |                                                                        |

      +--------------------------------------------------------------------+ */

      define event EmitMeta;

      start:

        put '<' $TABLENAME '>' CR ;

  ndent;

        break;

      finish:

        put '</' $TABLENAME '>' ;

        break;

      end;

      define event putby ;

        start:

        put "<snap>" NL;

  ndent;

        set $name  "order";      trigger EmitCol ;

        set $name  "date";      trigger EmitCol ;

        ndent;

  break;

  finish:

  put "</snap>" NL ;

  xdent;

        break;

      end;

      define event EmitRow;

         

        put "<detail>" CR ;

  ndent;

        set $name  "name";    trigger EmitCol ;

        set $name  "age";  trigger EmitCol ;

  xdent;

  put "</detail>" CR ;

  xdent;

        break;

      end;

      define event EmitCol;

        unset $value;

        set $value $col_values[$name];

        put '<'  $name '>' ;

        put      $value ;

        put '</' $name '>' CR ;

        break;

      end;

  end; /* custom */

run;

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

Hi:

  My guess is that you did not create the CUSTOM template first -- all of the templates used in that paper inherit from a template called CUSTOM.

  I just ran the programs from that paper today, using SAS 9.4, and both of the BY group programs worked for me. This is the Demo05 default output for a subset (with BY group processing used):

<?xml version="1.0" encoding="windows-1252" ?>

<TABLE>

   <BYGROUP REGION="Asian Pacific">

         <COSMETIC>

            <Subsidiary></Subsidiary>

            <Product></Product>

            <Date></Date>

            <Region></Region>

            <Stores></Stores>

            <Sales></Sales>

         </COSMETIC>

         <COSMETIC>

            <Subsidiary></Subsidiary>

            <Product></Product>

            <Date></Date>

            <Region></Region>

            <Stores></Stores>

            <Sales></Sales>

         </COSMETIC>

  ...rows deleted for space ...

   </BYGROUP NUMROWS="36">

   <BYGROUP REGION="Western Europe">

         <COSMETIC>

            <Subsidiary></Subsidiary>

            <Product></Product>

            <Date></Date>

            <Region></Region>

            <Stores></Stores>

            <Sales></Sales>

         </COSMETIC>

         <COSMETIC>

            <Subsidiary></Subsidiary>

            <Product></Product>

            <Date></Date>

            <Region></Region>

            <Stores></Stores>

            <Sales></Sales>

         </COSMETIC>

         <COSMETIC>

            <Subsidiary></Subsidiary>

            <Product></Product>

            <Date></Date>

            <Region></Region>

            <Stores></Stores>

            <Sales></Sales>

         </COSMETIC>

. . . rows deleted for space . . .

   </BYGROUP NUMROWS="144">

</TABLE>

and this is the other output created in the paper with the use of MVAR in the template the Demo06 program output:

<?xml version="1.0" encoding="windows-1252" ?>

<TABLE>

   <BYGROUP REGION="Asian Pacific" NUMROWS="36">

         <COSMETIC>

            <Subsidiary></Subsidiary>

            <Product></Product>

            <Date></Date>

            <Region></Region>

            <Stores></Stores>

            <Sales></Sales>

         </COSMETIC>

         <COSMETIC>

            <Subsidiary></Subsidiary>

            <Product></Product>

            <Date></Date>

            <Region></Region>

            <Stores></Stores>

            <Sales></Sales>

         </COSMETIC>

. . . rows deleted for space . . .

   </BYGROUP>

   <BYGROUP REGION="Western Europe" NUMROWS="144">

         <COSMETIC>

            <Subsidiary></Subsidiary>

            <Product></Product>

            <Date></Date>

            <Region></Region>

            <Stores></Stores>

            <Sales></Sales>

         </COSMETIC>

         <COSMETIC>

            <Subsidiary></Subsidiary>

            <Product></Product>

            <Date></Date>

            <Region></Region>

            <Stores></Stores>

            <Sales></Sales>

         </COSMETIC>

. . . rows deleted for space . . .

   </BYGROUP>

</TABLE>

  Since you do NOT show all of your template code, I can't see whether you inherit from CUSTOM template or not. If you look at that paper, on pages 9 and 10, and again on page 15, you will see that ALL of these templates ALL inherit from the CUSTOM template. In the front half of the paper, I explain about the CUSTOM template and that one has to be made first. What did you do for that PARENT= statement?? The "Custom" tagset template is NOT included in SASHELP.TMPLMST, so if you copied ALL the code, then you should have received an error/warning when the CUSTOM tagset template was not found.

  

  If you look on page 14 of the paper, it explains that the XML Libname Engine doesn't support by group processing. The use of the Macro variable technique was a demo of a technique that worked in SAS 9.1 -- the XML Libname engine has changed in SAS 9.2 and 9.3 and 9.4 so that you can now export hierarchical XML using an XML Map and you can use your XML Schema with the XML Mapper application to generate the model syntax you need for both import and export (something that was not available in SAS 9.1).
    

  The truth is that most folks started using the XML Mapper with their schema to do exporting after 9.1. So the techniques in that paper are 3 versions old and although the techniques all still produced output for me in 9.4, I would not use that technique for production work. The programs that go with that paper are not on the  Papers web site and I do not usually send out the programs for that paper anymore since the code was written for 9.1 and XML Map is more current.

  I would recommend working with Tech Support to do what you want to do. Writing XML with a data step program is a valid way to control the tags in an XML file. Using an XML Map is the newest way (since SAS 9.2, so the technique has been around for a while now)  to write custom XML. These days, I wouldn't recommend using PROC TEMPLATE code to write hierarchical XML because there is no guarantee that techniques that worked in SAS 9.1 will still keep working in future versions of the XML Libname engine..

cynthia

saskapa
Quartz | Level 8

Dear Cynthia,

Many thanks for your response. And nice to see that your code still works fine with 9.4

I do not have the full template called CUSTOM, in your paper it is only a partial Tagset code. ( is the full code in one of the programs that you are referring to in your paper ? Is there any link where to get the full template CUSTOM )

Otherwise, indeed, there was no use of a PARENT statement.

I am a bit sceptic about using XML Map to generate a customised hierarchical xml file.  I have spend time on it  to achieve want I needed without success. It looks like SAS XML can't produce any kind of hierarchical file.

Besides,  in one thread , as user seems to have contacted the technical support about hierarchical structures  :

"I spoke to SAS technical support. There is currently NO WAY to export into XML files with complex hierarchies using XML Mapper.

XML Mapper let´s you only export into a single table...which is not helpful. SAS tech support said that customers had to resolve to writing the XML document using PUT statements. ....."

This being said, I will contact the technical support and see if my structure can still be generated through  XML map and XML schema.

Regards,

Sas_kap

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1382 views
  • 3 likes
  • 2 in conversation