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_) (:) ;
run;
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.
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.
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.
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.
Thanks a lot. You are right.
Oleg
@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.
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.
Your code looks good or try
file "c:\work\out1.csv" dsd dlm='09'x recfm=n ;
recfm=n does not solves the problem.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.