Hello
I want to export SAS data set into XLSX.
I have tried 2 ways.
/***Way1-Work good***/
proc export data=Have
outfile="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/want.xlsx"
dbms=xlsx
replace;
sheet="CARS";
run;
/***Way2-Get message in EXCEL-We found a problem***/
libname RRR xlsx "/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/want.XLSX";
data Have;
set Have;
run;
libname RRR clear;
My question-
Why way2 is not working?
Here is the message I see when I try to open the XLSX file
I have never tried using a DATA step with no PUT statement to create Excel files, I doubt it will work because you are not writing anything to an output destination. However, after the ODS EXCEL command, PROC PRINT works.
Hi:
This is the form of the XLSX Libname usage we show in Programming 1:
Note how if I want to write anything to WANT.XLSX, I first need a LIBNAME statement that establishes the name of the workbook I want to create and associates RRR as the LIBREF with that workbook. Then, I need to use RRR.MyClass in the DATA statement and I need to have the input data (in this case, SASHELP.CLASS) in the SET statement. "MyClass" will become the name of the worksheet in the new workbook, because of the reference RRR.MyClass -- RRR points to WANT.XLSX and MyClass establishes the name of the sheet in this workbook.
If I want to write 2 sheets using this method, then I would do something like this:
Note how I use a WHERE statement in the second DATA step to filter the rows from SASHELP.SHOES that are going to be written to the WANT_TWO.XLSX workbook in the ShoeData worksheet.
Cynthia
/***Way2-Get message in EXCEL-We found a problem***/
libname RRR xlsx "/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/want.XLSX";
data Have;
/* very simple: you create WORK.HAVE from WORK.HAVE, so nothing ends up in the Excel file */
set Have;
run;
libname RRR clear;
Keep in mind that single-level dataset names end up in WORK (or USER, if it is defined).
You must do
libname rrr xlsx "/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/want.XLSX";
data rrr.have;
set have;
run;
libname rrr clear;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.