Help using Base SAS procedures

Exporting from SAS to Excel

Reply
N/A
Posts: 0

Exporting from SAS to Excel

hello ,

I have a SAS datafile called " sales.sas7bdat ". I used the code -

libname sc "c:\demaoA2.xls";

data sc.sales;
set work.sales;
run;
proc print data = sales;
run;

The datafile is all right and has the data but when i run the code then it gives an error in that the datafile is either corrupted or may be located in a location where it cant be read.

Is there something wrong with code.

regards ,
markc
SAS Super FREQ
Posts: 8,864

Re: Exporting from SAS to Excel

Posted in reply to deleted_user
Hi:
Given this code, you are using the SAS Libname engine for Excel to create the file c:\demaoA2.xls. You give this workbook a LIBREF of SC. Therefore, these statements:
[pre]data sc.sales;
set work.sales;
run;
[/pre]


will cause the creation of the demaoA2.xls file in write-mode. Then when your SET statement is encountered, SAS will copy the information from WORK.SALES into the Excel workbook -- in Excel binary form. This means, that until the file is CLOSED, you will not be able to open the file with Excel (when a file is open for WRITING, it cannot simultaneously be opened for READING -- therefore, when some application is holding a file open for creation purposes, generally the file is locked down into READ-ONLY mode). The SAS RUN statement does NOT close the Excel file. The error message you got tells you what the problem is.

The full message you get from Excel when you try to open a file that is still being held open by another application is:
"filename.xls" cannot be accessed. The file may be corrupted, located on a server that is not responding or read-only.

Some applications will give you the chance to go ahead and open the file in read-only mode. Excel is not one of those applications. It is very possessive of a file when the file is being written to. So, in order to CLOSE the Excel workbook, you must CLEAR your LIBNAME statement. The CLEAR option on the LIBNAME statement closes the workbook connection so the workbook can be opened or moved. This means that WITHOUT a CLEAR for the LIBREF, that SAS is holding the file open in WRITE-mode or READ-ONLY mode (as the error suggests).

When I run this code in my WINDOWS session, using SASHELP.SHOES, I am only able to open the Excel Workbook AFTER the LIBNAME has been cleared. (the very last statement in the program below).

cynthia

[pre]
libname test excel "c:\temp\testshoes.xls";

data test.shoes;
set sashelp.shoes;
run;

proc print data = test.shoes;
run;

libname test clear;
[/pre]
Ask a Question
Discussion stats
  • 1 reply
  • 135 views
  • 0 likes
  • 2 in conversation