topic Re: How to output data in same line PUT statement in SAS Programming

How to output data in same line PUT statement

ea33 — Wed, 12 May 2021 15:11:29 GMT

I need to parse an XML that is stored in a database column (the whole XML in 1 column). To get around the 32K string limit, I've split up the xml to multiple parts in a query and then tried to place them in one file for processing. Code below:

CREATE TABLE work.table AS
SELECT * FROM connection to src(
SELECT date, market, 
	substring(content, 1, 30000) as xml_1,
	substring(content, 30001, 30000) as xml_2,
	substring(content, 60001, 30000) as xml_3,
	substring(content, 90001, 30000) as xml_4,
	substring(content, 120001, 30000) as xml_5,
	substring(content, 150001, 30000) as xml_6,
	substring(content, 180001, 30000) as xml_7,
	substring(content, 210001, 30000) as xml_8,
	substring(content, 240001, 30000) as xml_9
FROM table); disconnect from src; Quit;

filename xml_file 'path/xml.xml';

data _null_;
set table_from_query;
output;
file xml_file;
put xml_1 xml_2 xml_3 xml_4 ... xml_9;
run;

It all works as I expect it, but when I look at the xml created "xml_file" In the places that they were joined (xml_i ends and xml_i+1 starts) they are not placed on the same line. Some breaks do not cause errors, but some do as they split the actual tag. Example below:

How can I start the new input on the same line as before? I've tried @ after put, but it is the same result.

Any help is appreciated. Side note: put xml_: doesn't work instead of naming all xml_1 through xml_9.

Re: How to output data in same line PUT statement

Tom — Wed, 12 May 2021 15:41:19 GMT

Two issues.

First the default logical record length (LRECL) is 32,767 bytes. You are trying to write 9*30,000 bytes.

Second if you use list mode output then the PUT statement will insert a space between the values.

So set a longer record length and use formatted output.

Simple version. Not sure why you had the FILENAME statement but I removed it make example shorter. Use the $CHAR format to preserve any leading spaces on any of the sub strings.

data _null_;
  set table_from_query;
  file 'path/xml.xml' lrecl=1000000;
  put (xml_1-xml_9) ($char30000.) ;
run;

SAS can normally write lines at least as long a 1,000,000 bytes. The actual limit probably varies by version and operating system and possibly even computer memory available. If you need longer use RECFM=N instead. That should also remove the line breaks between observations. Which you might or might not want to do.

If you are actually including the line breaks between observations then you can probably remove the trailing spaces using $VARYING format, but you would need to figure out where the string actually ends. For example by find the last non-empty string.

data _null_;
  set table_from_query;
  file 'path/xml.xml' lrecl=1000000;
  array xml_ [9] ;
  do last=dim(xml_) to 1 by -1 while(missing(xml_[last])); end;
  if last then do;
    do index=1 to last-1 ;
      put xml_[index] $char30000. @;
    end;
    length=lengthn(xml_[last]);
    put xml_[last] $varying30000. length @;
  end;
  put;
run;

Re: How to output data in same line PUT statement

ea33 — Wed, 12 May 2021 15:44:12 GMT

Thank you, Tom. This did exactly what I wanted it to do.