03-02-2016 06:06 AM
My SAS and MS Excel have different bit counts so I used PC File Server to read from an excel file.
However, the Excel data does not have a header row with variable names so I used header=no which assigns the default variable names F1, F2, F3. See the code below.
libname xlsdata pcfiles 'custcaus.xls' header=no;
When I run the code above, I get an error message not recognising 'header=no'.
'header=no' cannot be used with pcfiles? if not, what should I use?
03-02-2016 07:35 AM
So your PC Files Server is on the same machine as the SAS session executes, right?
Have you tried an Excel file with headers, does that work?
Are you sure about the header= option? I can't find it in the online doc related to PC Files libname.
03-02-2016 07:49 AM
I don't know the pcfiles libname statement, however have you tried:
libname xlsdata excel 'custcaus.xls' header=no;
As header only seems to be associated with Excel file format, so not pcfiles general.
Also, as always, I would suggest saving the data into CSV, and then datastep reading in. Excel is not a good data transfer format.
03-02-2016 06:35 PM
I have not found a way to do this using the libname statment for pcfiles. However, you do seem to be able to use a proc import using the xlsx dbms engine. I am running SAS 9.4.
proc import datafile = "\\server\AGC-4 Specimen List.xlsx"
out = work.h1 replace;
range= "NBG-18 Piggyback$A1:B7"n ;
The xlsx engine seems to avoid the 32bit / 64bit problems and allows the use of getnames=no.
Way at the bottom of this page (link below) is a description of the allowable options for the xlsx dbms engine. This is SAS 9.3, so it may very well depend on what version you are running.
03-03-2016 10:06 AM
In response to Ksharp, the excelcs dbms option does not have the option of "getnames=no". So you end up with the first row of the range being made into column names. The xlsx dbms option is the only one that works with the 32 bit/ 64 bit issue and also allows the use of the "getnames=no" option.
03-03-2016 02:27 AM
03-03-2016 10:27 AM
The real point of this exercise is to be able to read an unkown number of sheets of unkown name inside a workbook. By making the Excel workbook a library, you can use proc datasets to write the list of sheets to a SAS table. Then you can use a macro to loop thorugh the table and read all the sheets without having to know before hand how many there are or what their names are. The follow script works. It just seems that once you have established the workbook as a library, you shouldn't have to close the library and use a different approach to access the spreadsheets. However, this does work quite well.
libname mylib pcfiles type=Excel path="\\server\excel.xlsx" ;
proc datasets library=mylib ;
contents data=mylib._all_ out=work.sheetlist;
create table work.sheets as
compress(memname, "'") as sheet
, compress(memname, "'$ -") as table
where memname not contains "#Print";
libname mylib clear;
%DO i = 1 %TO &nsheet;
set work.sheets point=obs;
call symput ('insheet', trim(sheet));
call symput ('outfile', trim(table));
%put Now reading &i &insheet to &outfile;
proc import datafile = "\\server\excel.xlsx"
out = work.&outfile replace;
range= "&insheet.A1:B7"n ;