Desktop productivity for business analysts and programmers

How do I Export to XML

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

How do I Export to XML

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!

 

 


Accepted Solutions
Solution
‎11-10-2017 09:38 AM
Occasional Contributor
Posts: 9

Re: How do I Export to XML

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.. Smiley Very Happy )

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! Smiley Happy

View solution in original post


All Replies
Super User
Super User
Posts: 8,153

Re: How do I Export to XML

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;
Solution
‎11-10-2017 09:38 AM
Occasional Contributor
Posts: 9

Re: How do I Export to XML

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.. Smiley Very Happy )

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! Smiley Happy

Super User
Super User
Posts: 8,153

Re: How do I Export to XML

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 313 views
  • 4 likes
  • 2 in conversation