topic Re: Reading xml into single cell in SAS Programming

Reading xml into single cell

Lars_Beck — Wed, 25 Sep 2024 18:48:05 GMT

Hi,

I am trying to read an xml file into a SAS dataset but I am struggeling to get all the xml code to fit into one single cell. Instead I am getting one row for every "line" in the xml document.

I've been using the infile statement. Perhaps there is an option I am missing?

data test;
infile "\\myfile.xml";
format line $2000.;
input;
line = _infile_;
run;

Cheers

Re: Reading xml into single cell

ballardw — Wed, 25 Sep 2024 19:10:34 GMT

Doesn't make sense to me why you want the entire contents of a file is one observation and a single variable (SAS data sets do not have "cells").

You can try using a different TERMSTR setting on the Infile statement than is valid for your operating system. That option is what tells SAS you have reached the "end of a line" in a text file.

If your operating system is Windows then the default characters for end of line are carriage-return and line-feed, if a Unix derivative the end of line is a line-feed.

So you might try Termstr=CR which is unlikely to be the actual end of line character unless this started on an Apple computer.

data test;
infile "\\myfile.xml"  Termstr=CR ;
format line $2000.;
input;
line = _infile_;
run;

How sure are you that "all of the file" will fit in 2000 characters?

You may have increase the LRECL on the infile

What will you do with that resulting, pretty sure to be moderately ugly, variable?

Re: Reading xml into single cell

Lars_Beck — Wed, 25 Sep 2024 19:26:56 GMT

Thank you. It seems to work.

I might extend the length of the variable if I need to.

The purpose is to read several files in this way and use regex to find the relevant information. That way I will get one row for each xml.

Re: Reading xml into single cell

ballardw — Wed, 25 Sep 2024 20:19:10 GMT

I might be tempted to start with the XML library

Libname xmlin xml '\\myfile.xml';

and then take a look at that library. If the file is clean enough you should find a data set of some sort.

Then

data work.somedata;

set xmlin.somedata;

run;

would copy that data set from the XML to the work library (or easily extend to a different library).

Might be a lot easier than dealing with lots of REGEX code to parse.

Re: Reading xml into single cell

Tom — Wed, 25 Sep 2024 20:31:03 GMT

Read the file as BINARY instead of LINES of TEXT.

So use RECFM=F or RECFM=N.