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

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.

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
  • 5 replies
  • 860 views
  • 1 like
  • 2 in conversation