DATA Step, Macro, Functions and more

header=no works for PC File Server?

Reply
New Contributor
Posts: 3

header=no works for PC File Server?

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
Super User
Posts: 5,257

Re: header=no works for PC File Server?

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
New Contributor
Posts: 3

Re: header=no works for PC File Server?

Yes, PC File Server works fine. But when I add “header=no” does not work.

Thanks..



Super User
Super User
Posts: 7,404

Re: header=no works for PC File Server?

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.

New Contributor
Posts: 3

Re: header=no works for PC File Server?

Thanks..

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

Super User
Super User
Posts: 7,404

Re: header=no works for PC File Server?

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

Occasional Contributor
Posts: 9

Re: header=no works for PC File Server?

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.

 

Super User
Posts: 9,681

Re: header=no works for PC File Server?

Can you use proc import + dbms=excelcs  ?

Occasional Contributor
Posts: 9

Re: header=no works for PC File Server?

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.

Super User
Posts: 5,257

Re: header=no works for PC File Server?

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
Occasional Contributor
Posts: 9

Re: header=no works for PC File Server?

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;

Ask a Question
Discussion stats
  • 10 replies
  • 441 views
  • 0 likes
  • 5 in conversation