SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AlexMoreton
Obsidian | Level 7

Hi all,

Thanks to help in this community, I was able to extract a super large file from a SQL database and export it into XML format (required by my teacher). I was able to get each row's value (in string) and concatenate them into one file to be read.

Right now I'm trying to get it to be read by a XML reader such as XML Notepad. However, as each row has the same root tag, I need to create a new tag

So right now i have many strings with the same root tag.

<Interview>Row1</Interview>

<Interview>Row2</Interview>

<Interview>Row3</Interview>

 

Once I add a tag before them it can be read, but I would like to be able to just code for it to do so automatically.

to look like this:

<Hi>

<Interview>Row1</Interview>

<Interview>Row2</Interview>

<Interview>Row3</Interview>

</Hi>

 

As for the code I simply just used this:

data Complete;
file alex lrecl=90000;
set Alex.Interview;                   *this library already had all the rows in the database and only has one variable which is the interview                                                         which I want to merge and become one ;

Run;

 

I know the code is basically useless but right now I dont really know what to do... This DOES create one XML file that has all the contents I want in it. But it lacks the tag so XML reader can read it.

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

There is nothing in that code you post which an XML file, or in fact any physical file other than a dataset?

 

Maybe you need something like:

data _null_;
  set alex.interview end=last;
  file "want.xml";
  if _n_=1 then do;
    put "<Root>";
    put "<Hi>";
  end;
  tmp=cat("<Interview>",yourstring,"</Interview>");
  put tmp;
  if last then put "</Root>";
run;

But its very hard to tell.  Yo know how to make a well structured XML file I presume, it has a line at the top stating version, encoding etc., then open a tag which is the root, and at end close the root tag...

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

There is nothing in that code you post which an XML file, or in fact any physical file other than a dataset?

 

Maybe you need something like:

data _null_;
  set alex.interview end=last;
  file "want.xml";
  if _n_=1 then do;
    put "<Root>";
    put "<Hi>";
  end;
  tmp=cat("<Interview>",yourstring,"</Interview>");
  put tmp;
  if last then put "</Root>";
run;

But its very hard to tell.  Yo know how to make a well structured XML file I presume, it has a line at the top stating version, encoding etc., then open a tag which is the root, and at end close the root tag...

AlexMoreton
Obsidian | Level 7

Hi RW9, I forgot to add in the FILENAME line in the beginning which would have created it into a xml file.

I know that the XML files have that format in which it has those things at the beginning. Which makes me think, should I try and create the tags in a data step that i set alex.interview and just actually add <Hi> before _INTERVIEW_ and </Hi> after it? would that be a possible way?

Thanks for the help so far!

AlexMoreton
Obsidian | Level 7

Thanks RW9,

output's not really complicated, I just need to add a tag in front and after the end of the total dataset at alex.interview. Then I can export it and it should be readable on XML Notepad etc. It's just right now I don't know how to do so...  

the output is basically the same except it will have a new tag in front and at the end of the dataset. which would be <hi> </hi>. Is this not really a good idea to do so? because basically I have rows with the same tags <interview> </interview>. So I just need to create a new tag to become the new root.

Thanks for the advise though! I'll keep trying because it feels like I'm only one step away from making this (which I can do manually, but I just think there HAS to be a code for this).

Thank you RW9

AlexMoreton
Obsidian | Level 7

Thank you RW9,

I played around your code and I managed to get it to do exactly what I wanted. Thank you

filename alex 'X:\ALEX TESTING\TRY5.xml';

data tags;
set work.complete end=last;
file alex lrecl=90000;
if _n_=1 then do;
put "<Hi>";
end;

put Interview;

if last then put "</Hi>";
run;

I'll accept your answer as the answer but this is just in case you wanted to see. 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1442 views
  • 1 like
  • 2 in conversation