BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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

Ronein_0-1704107324896.png

 

 

 

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Cynthia_sas
SAS Super FREQ

Hi:

  This is the form of the XLSX Libname usage we show in Programming 1:

Cynthia_sas_0-1704121745745.png

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:

Cynthia_sas_1-1704122271366.png

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

Kurt_Bremser
Super User
/***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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 3 replies
  • 370 views
  • 3 likes
  • 4 in conversation