topic Re: Line breaks in quoted strings export csv in SAS Studio

Line breaks in quoted strings export csv

PWS — Wed, 20 Dec 2017 15:43:39 GMT

Hello,

I'm trying to find an answer for avoiding line breaks/line feeds in an csv output file.

So I want to export a file with multiple columns. One of them is a json string with a lot of qoutes.

It looks like this;

data _null_ ;
/* FILE "&file_export_wait_s" DLM= ';' ; */
set expclang.&_file_name._export;
FILE "&file_export_wait_s" DLM= ';';

if _n_ = 1 then put @1 'CUSTOMER_ID;SUBJECTLINE;PREHEADER;REFERRER;FLEXBANNERS;GLOBALIDS;TEXTS;CONTENT'; /* Add variable names */
put CUSTOMER_ID SUBJECTLINE PREHEADER REFERRER FLEXBANNERS GLOBALIDS TEXTS CONTENT ;
/* put (_all_) (~);*/

run ;

The content is a huge string with json in it. What happens in the csv is that there are linebreaks. How can avoid those?

Thanks

Re: Line breaks in quoted strings export csv

Kurt_Bremser — Wed, 20 Dec 2017 15:49:19 GMT

Try adding a sufficient lrecl= option to your file statement.

Re: Line breaks in quoted strings export csv

Reeza — Wed, 20 Dec 2017 15:52:09 GMT

@PWS wrote:

What happens in the csv is that there are linebreaks. How can avoid those?

Thanks

What does that mean in detail please? Trying to understand your problem 🙂

Re: Line breaks in quoted strings export csv

PWS — Wed, 20 Dec 2017 17:00:14 GMT

When writing the file to csv a lot of text is written in the file. Especially for content, because there is a json string included, looking like this;

see attached file

So one line should start with slgnt19 and the next line should also start with slgnt19.

Can't exactly find yet where to put the lrecl= option

Re: Line breaks in quoted strings export csv

Reeza — Wed, 20 Dec 2017 17:03:31 GMT

@PWS wrote:

Can't exactly find yet where to put the lrecl= option

On the FILE statement

http://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n15o12lpyoe4gfn1y1vcp6xs6966.htm&docsetVersion=9.4&locale=en

Re: Line breaks in quoted strings export csv

Reeza — Wed, 20 Dec 2017 17:07:54 GMT

CSV means comma delimited, literally, Comma Separated Values. Why are you using ; as a delimiter if you want a CSV file?

Re: Line breaks in quoted strings export csv

PWS — Wed, 20 Dec 2017 18:21:27 GMT

Good question. For now it's because the receiving party is requesting this delimiter.

Re: Line breaks in quoted strings export csv

PWS — Wed, 20 Dec 2017 18:30:05 GMT

Thanks! It worked...

Re: Line breaks in quoted strings export csv

PWS — Wed, 20 Dec 2017 18:31:14 GMT

Thanks! It works

Re: Line breaks in quoted strings export csv

PGStats — Wed, 20 Dec 2017 18:46:44 GMT

You could replace all control characters with spaces with something like:

TEXTS = prxchange("s/[[:cntrl:]]+/ /o", -1, TEXTS);

Re: Line breaks in quoted strings export csv

PGStats — Wed, 20 Dec 2017 18:51:19 GMT

The delimiter in a CSV file (despite the name) is locale dependent. It is typically the semicolon in French.