SAS XLSX engine to export

[Ronein]

Hello

Data set ttt has some char columns with lenght 300.

I am trying to export it to XLS file using XLSX libname engine.

However when I try to open the XLSX file I get a message 

I tried to use SASHELP.cars  data set and it is working well.

I think that   char fields with long format cause problems .

Any solution ?

[Tom]

Are you trying to make an XLS file or an XLSX file.

Did you close the libref after writing to the XLSX file before trying to open it with Excel?

Please show the lines from the SAS log for the code that is creating the XLSX file.  Make sure to copy as text (not photograph) and paste in using the Insert Code button so that formatting is preserved.

[Ronein]

1- I want to create XLSX FILE

2-I didn't close the libref after writing to the XLSX file before trying to open it with Excel.

May you show please the code how to do it?

3-When I run the following code then in SAS a new window is created called  "Output Data" and I get also a message that 

What is the way to prevent from "output data" window to be created? (I don't need this window because I just  want to export it to XLSX file)

[Tom]

Look at code posted by @Cynthia_sas with examples of how to close the libref.

The window you posted is talking about opening the SAS log, not the output. Why would your LOG get so large?  Are you doing other SAS steps?

I don't know how Enterprise Guide deals with opening actual outputs.  I doubt it would try to open the file if you use the XLSX libname engine to create it.  If you use the ODS EXCEL method then you might need to add code to turn off other ODS destinations to prevent also generating HTML, PDF or other outputs that are open.

[Cynthia_sas]

Hi:

  I don't have that issue when I use the XLSX libname engine or ODS EXCEL in 9.4M7 version. This code works for me:

** Method 1: DATA step and PROC EXPORT;
data testit (keep=newvar name age sex height weight);
  length newvar $300 x $55;
  set sashelp.class;
  where age le 12;
  x='!abcdefghijklmnopqrstuvwxyz~ABCDEFGHIJKLMNOPQRSTUVWXYZ!';
  newvar=catx('~','1', x, '2', x, '3', x, '4', x, '5',x,'0123456789' );
  testlen=length(newvar);
run;

proc export outfile='c:\temp\longvar_test1.xlsx'
            data=work.testit 
			dbms=xlsx;
run;

** Method 2: PROC PRINT with ODS EXCEL using original WORK.TESTIT dataset;
ods excel file='c:\temp\longvar_odstest2.xlsx';
proc print data=work.testit noobs;
run;
ods excel close;


** Method 3: PROC EXPORT from datastep with libname engine;
libname myxl xlsx 'c:\temp\longvar_test3.xlsx';
data myxl.longvar (keep=newvar name age sex height weight);
  length newvar $300 x $55;
  set sashelp.class;
  where age le 12;
  x='!abcdefghijklmnopqrstuvwxyz~ABCDEFGHIJKLMNOPQRSTUVWXYZ!';
  newvar=catx('~','1', x, '2', x, '3', x, '4', x, '5',x,'0123456789' );
  testlen=length(newvar);
run;

libname myxl close;

I used the XLSX engine with PROC EXPORT and I used the XLSX engine with the LIBNAME statement and in both instances the Excel file opened for me. I also included the ODS EXCEL method (which also creates an XLSX file), in the interest of completeness.

Here's what the output looked like:

Method 1 (PROC EXPORT):

Method 2 (ODS):

Method 3 (LIBNAME engine with DATA step):

You do see a style difference between the ODS method (2) and the other methods, but the big character string worked for me with the XLSX libname engine or with ODS.

Cynthia

[Sajid01]

Hello @Ronein 
1.Excel allows 32767 characters in a cell.

(https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-2...)

2.Simply click yes and open your spreadsheet. You should not have any issue most cases.

[Ronein]

Thanks, Method 3 is not working for me.

libname myxl xlsx 'path/class.xlsx';
data myxl.longvar (keep=newvar name age sex height weight);
  length newvar $300 x $55;
  set sashelp.class;
  where age le 12;
  x='!abcdefghijklmnopqrstuvwxyz~ABCDEFGHIJKLMNOPQRSTUVWXYZ!';
  newvar=catx('~','1', x, '2', x, '3', x, '4', x, '5',x,'0123456789' );
  testlen=length(newvar);
run;
libname myxl close;

The error is:
36         libname myxl close;
ERROR: The CLOSE engine cannot be found.
ERROR: Error in the LIBNAME statement.

[Tom]

I get that error all of the time.  Use CLEAR instead of CLOSE.  Or just use:

libname myxl ;

[Ronein]

When I run the following code to create XLSX file then the following things happened:

1-Output Data window was created

2-I get warning

3-When I am truing to open the XLSX file I get an error

Then I click yes and get

Than I get another error

libname XLOut xlsx "/path/ere.xlsx"; 
data XLOut.sheet1;  
SET ttt;
run;

[Cynthia_sas]

Hi:

  Sorry, I had ODS CLOSE on the brain. You need to use CLEAR, not CLOSE:

Also, remember that the LIBNAME statement reference has to match. So I define the MYXL library in the top LIBNAME statement and I clear the MYXL library in the bottom LIBNAME statement.

Cynthia