DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 151
Accepted Solution

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

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;

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_) (Smiley Happy ;

run;


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 7,392

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

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.

View solution in original post


All Replies
Super User
Posts: 6,928

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 151

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

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.

Solution
3 weeks ago
Super User
Super User
Posts: 7,392

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

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.

Regular Contributor
Posts: 151

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

Thanks a lot. You are right.

 

Oleg

Super User
Posts: 6,928

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


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 151

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

That's right.
Thank you.

Oleg
Super User
Posts: 6,928

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 151

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

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.
Super User
Posts: 9,671

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

Your code looks good or try

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

Regular Contributor
Posts: 151

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

recfm=n does not solves the problem.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 127 views
  • 0 likes
  • 4 in conversation