topic Re: How to export large number of variables to csv without wrapping the lines in SAS Programming

How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 12:17:19 GMT

Dear All,

I need to export SAS dataset that exceeds linesize 32767 to csv.

How can I do it?

I use SAS 9.4.

My code is below.

Many thanks,

Oleg

data test;

array MY_VARIABLE_NAME_[1000] $100;

retain MY_VARIABLE_NAME_ '1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';

output;

run;

proc transpose data=test (obs=0) out=names;

var _all_;

run;

data _null_;

set names ;

file "c:\work\out1.csv" dsd dlm='09'x lrecl=500000 ;

put _name_ @;

run;

data _null_;

set test;

file "c:\work\out1.csv" dsd dlm='09'x lrecl=500000 MOD ;

put (_all_) (:) ;

run;

Re: How to export large number of variables to csv without wrapping the lines

Kurt_Bremser — Tue, 25 Jul 2017 12:40:31 GMT

And where is your problem? Your code worked well when I tested it.

Use the "code" subwindows for posting code (6th and 7th button right on top of the main posting window) to avoid reformatting of some character sequences into smileys and artificial linefeeds in the code.

Re: How to export large number of variables to csv without wrapping the lines

Ksharp — Tue, 25 Jul 2017 12:45:51 GMT

Your code looks good or try

file "c:\work\out1.csv" dsd dlm='09'x recfm=n ;

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 12:48:34 GMT

When I view the output csv in a file manager or try open it in excel the recordes are wrapped when them exceed 32767 characters.

That is the problem.

I do not know how to avoid it.

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 12:56:56 GMT

recfm=n does not solves the problem.

Re: How to export large number of variables to csv without wrapping the lines

RW9 — Tue, 25 Jul 2017 13:02:03 GMT

Why are you specifying:

data _null_;
set names ;
file "c:\work\out1.csv" dsd dlm='09'x lrecl=500000 ;

dlm='09'x? That is a tab character. CSV means Comma Separated Variable file. This is why you have issues, Excel will open a CSV and parse it, however it does not recognise a tab delimited file. Also, text editors will wrap lines and such like as part of their options. So step one, be clear about what you want the output to llok like, if its CSV, use a Comma between data items, if its tab then don't call it CSV, and when opening in Excel you will need to do text to columns and use tab as delimiter.

Re: How to export large number of variables to csv without wrapping the lines

Kurt_Bremser — Tue, 25 Jul 2017 13:02:26 GMT

@Oleg_L wrote:

When I view the output csv in a file manager or try open it in excel the recordes are wrapped when them exceed 32767 characters.

That is the problem.

I do not know how to avoid it.

That's cleary the problem of the file manager or Excel. I just checked my results file with notepad++, and it has 2 lines of length 101000.

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 13:27:38 GMT

Thanks a lot. You are right.

Oleg

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 13:28:55 GMT

That's right.
Thank you.

Oleg

Re: How to export large number of variables to csv without wrapping the lines

Kurt_Bremser — Tue, 25 Jul 2017 13:41:39 GMT

Just for fun:

I changed the delimiter to a comma, and opened the resulting file with LibreOffice and Excel.

Excel wrapped the lines, while LibreOffice opened the file as is without any problems (last columns 'ALK' and 'ALL', so all columns were imported).

Draw your own conclusions.

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 15:19:15 GMT

I changed file extension to txt and opened the result with Excel through text import wizard. Excel opened the file fine this way, while there was a problem to open via text to columns.