BookmarkSubscribeRSS Feed
hoon0338
Calcite | Level 5

Hi,

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?

 

Thanks

PY


Screen Shot 2016-03-02 at 11.01.42.png
10 REPLIES 10
LinusH
Tourmaline | Level 20

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.

Data never sleeps
hoon0338
Calcite | Level 5
Yes, PC File Server works fine. But when I add “header=no” does not work.

Thanks..



RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

hoon0338
Calcite | Level 5
Thanks..

The bit counts of my SAS and MS Office are different so I cannot use ‘excel’ but ‘pcfiles’

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Even more reason to use a proper transfer format like CSV or XML rather than a proprietary software not designed for the purpose.

hulllc
Calcite | Level 5

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"

dbms=xlsx

out = work.h1 replace;

getnames=no;

range= "NBG-18 Piggyback$A1:B7"n ;

run;

 

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. 

 

http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...

 

Good luck.

 

Ksharp
Super User

Can you use proc import + dbms=excelcs  ?

hulllc
Calcite | Level 5

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.

LinusH
Tourmaline | Level 20
Is/will this be a regular delivery?
If not: add headers to the Excel file manually.
If yes: require a different file layout from the delivering system/responsible party. And then, like @RW9 says, try to get a csv file instead.
Data never sleeps
hulllc
Calcite | Level 5

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;
run;

 

proc sql;
create table work.sheets as
select distinct
compress(memname, "'") as sheet
, compress(memname, "'$ -") as table
from work.sheetlist
where memname not contains "#Print";
; quit;

 

%let nsheet=&sqlobs;

libname mylib clear;

 

%MACRO LOOP;
%DO i = 1 %TO &nsheet;

data _NULL_;
obs=&i ;
set work.sheets point=obs;
call symput ('insheet', trim(sheet));
call symput ('outfile', trim(table));
stop;
run;

 

%put Now reading &i &insheet to &outfile;

 

proc import datafile = "\\server\excel.xlsx"
dbms=xlsx
out = work.&outfile replace;
getnames=no;
range= "&insheet.A1:B7"n ;
run;


%END;

%MEND loop;

%LOOP;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 10 replies
  • 2576 views
  • 0 likes
  • 5 in conversation