BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Oleg_L
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

10 REPLIES 10
Kurt_Bremser
Super User

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.

Oleg_L
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Oleg_L
Obsidian | Level 7

Thanks a lot. You are right.

 

Oleg

Kurt_Bremser
Super User

@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.

Oleg_L
Obsidian | Level 7
That's right.
Thank you.

Oleg
Kurt_Bremser
Super User

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.

Oleg_L
Obsidian | Level 7
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.
Ksharp
Super User

Your code looks good or try

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

Oleg_L
Obsidian | Level 7

recfm=n does not solves the problem.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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