How do I Export to XML

[Kran]

Hello dear experts,

I need to export datasets into xml files and need some help. I am on SAS 9.4.

Example DATA from my datasets in tabular form structure:

ServiceNumber  CustomerDetails  NumOfServices  ServiceDetails1  ServiceDetails2  AdditionalDetails

         123                    aaa                          1                      x                         y                           z

         124                    bbb                          2                      x                         y                           z

         124                    bbb                          2                      x                         y                           z

         125                    ddd                          1                      x                         y                           z

Example DATA format expected in XML:

  <?xml version="1.0" encoding="xxxxxxxx" ?>
- <DataofServices>
       -  <Services>
              <ServiceNumber>123</ServiceNumber>
              <CustomerDetails>aaa</CustomerDetails>
              <NumOfServices>1</NumOfServices>
               -  <ServiceDetails>

                             <ServiceDetails1>x</ServiceDetails1>
                             <ServiceDetails2>y</ServiceDetails2>
                  </ServiceDetails>

              <AdditionalDetails>z</AdditionalDetails>

        </Services>
       -  <Services>
              <ServiceNumber>124</ServiceNumber>
              <CustomerDetails>bbb</CustomerDetails>
              <NumOfServices>2</NumOfServices>
               -  <ServiceDetails>

                            <ServiceDetails1>x</ServiceDetails1>
                            <ServiceDetails2>y</ServiceDetails2>
                   </ServiceDetails>

               -  <ServiceDetails>

                            <ServiceDetails1>x</ServiceDetails1>
                            <ServiceDetails2>y</ServiceDetails2>
                   </ServiceDetails>

              <AdditionalDetails>z</AdditionalDetails>

        </Services>

  </DataofServices>

From what I have tried I am able to output the xml with a simple hierarchy, but not the nested type xml where multiple entries can exist based on data count in the third column as shown in the sample.

As you can see, the ServiceDetails1&2 get nested under ServideDetails based on the count value of NumOfServices.

XML generation is new to me so I searched online, but it seems that most information is for importing XML and most information on exporting is just to export the table line-by-line in XML, not the nested way with differing number of nested values as above.

Pls help and Thanks in advance!

[RW9]

If you want a more complicated XML output, then you will likely need to write this yourself using datastep and put statements.  At the end of the day, XML is just plain text with tags, so you can create it any way you like.  Some pseudocode to get you going:

data _null_;
  set have end=last;
  file "c:\want.xml";
  array servicedetails{*};
  if _n_=1 then do;
    put '<?xml version="1.0" enconding="xxxxx" ?>';
    put '<DataofServices>';
  end;
  put '<ServiceNumber>' put(servicenumber,z3.) '</ServiceNumber>';
  ...
  do i=1 to dim(servicedetails);
    put '<ServiceDetails>' sericedetails{i} '</ServiceDetails>';
  end;
  ...
  if last then put '</DataofServices>';
run;

[Kran · Accepted solution]

Hey thanks for your reply CW9..

so I tried it this below way..

PROC TEMPLATE;
DEFINE TAGSET Tagsets.xmlexport /STORE=work.templat; INDENT=5;
  DEFINE EVENT doc;
    START:
    /*put required declarations in XML*/
       PUT  "<?xml version=""1.0""";
       PUTQ " encoding=" ENCODING;
       PUT  " ?>";
       PUT  "<!--";
       PUT "  XML File for the Services Details";
       PUT "  -->";
       PUT "<Servs>";
       EVAL $counter 0;
       EVAL $curr_claim "-";
    FINISH:
  PUT "</Serv>";
  PUT "</Servs>";
 END;
 

DEFINE EVENT row;
  START:
     TRIGGER data /IF CMP(section,"body");
 END;
 

DEFINE EVENT data;
   START:
       DO /IF CMP(name,"ServiceNumber");
           DO /IF CMP($curr_claim,"-") or (^CMP($curr_claim,"-") and ^CMP($curr_claim,value));
               EVAL $line_num 1;
               PUT "</Serv>" /IF ^CMP($curr_claim,"-");
               SET $curr_claim value;
               PUT "<Serv>";
               PUT "<ServiceNumber>" VALUE "</ServiceNumber>";
               PUT "<CustomerDetails>" VALUE "</CustomerDetails>";
               PUT "<NumOfServices>" VALUE "</NumOfServices>";
               PUT "<ServiceDetails>";
            ELSE;
            DO /IF CMP($curr_claim,value);
               EVAL $line_num $line_num + 1;
               PUT "<Details>";
           DONE;
        DONE;
     DONE;
     PUT "<ServiceDetails1>" VALUE /if cmp(name,"ServiceDetails1");
     PUT "</ServiceDetails1>" /if cmp(name,"ServiceDetails1");
     PUT "<ServiceDetails2>" VALUE /if cmp(name,"ServiceDetails2");
     PUT "</ServiceDetails2>" /if cmp(name,"ServiceDetails2");
     PUT "</Details>" /IF CMP(name,"ServiceDetails2");
     PUT "<AdditionalDetails>" VALUE /if cmp(name,"AdditionalDetails");
     PUT "</AdditionalDetails>" /if cmp(name,"AdditionalDetails");

  END;
END;                                                                       
RUN;

This above code seems to work for multiple service details nested in ...!, but I keep getting same value as ServiceNumber for CustomerDetails and NumOfServices (and yes I know why.. 😄 )

but I need help with how to get them out of the do/if condition for the service details part ..but yet, come in the order as required xml file..

<Serv>
           <ServiceNumber>123</ServiceNumber>
           <CustomerDetails>aaa</CustomerDetails>
           <NumOfServices>1</NumOfServices>
           -  <ServiceDetails>
                          <ServiceDetails1>x</ServiceDetails1>
                          <ServiceDetails2>y</ServiceDetails2>
              </ServiceDetails>
           <AdditionalDetails>z</AdditionalDetails>

</Serv>

Thanks in advance! 🙂

[RW9]

Please avoid coding all in upcase, it really makes code hard to read and via the web just looks like shouting.

I had not realised you wanted to do this via template code, I am afraid I have not done that so can't help there, the code I provided was a simple datastep write, then you can use looping and such like.  I know looping effect is possible in template, but as I haven't done any in ages it would mean reading up on the subject again.