topic Re: header=no works for PC File Server? in SAS Programming

header=no works for PC File Server?

hoon0338 — Wed, 02 Mar 2016 11:06:23 GMT

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

Re: header=no works for PC File Server?

LinusH — Wed, 02 Mar 2016 12:35:01 GMT

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.

Re: header=no works for PC File Server?

hoon0338 — Wed, 02 Mar 2016 12:37:34 GMT

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

Thanks..

Re: header=no works for PC File Server?

RW9 — Wed, 02 Mar 2016 12:49:09 GMT

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.

Re: header=no works for PC File Server?

hoon0338 — Wed, 02 Mar 2016 12:54:34 GMT

Thanks..

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

Re: header=no works for PC File Server?

RW9 — Wed, 02 Mar 2016 13:06:30 GMT

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

Re: header=no works for PC File Server?

hulllc — Wed, 02 Mar 2016 23:35:26 GMT

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#n0msy4hy1so0ren1acm90iijxn8j.htm

Good luck.

Re: header=no works for PC File Server?

Ksharp — Thu, 03 Mar 2016 02:58:49 GMT

Can you use proc import + dbms=excelcs ?

Re: header=no works for PC File Server?

LinusH — Thu, 03 Mar 2016 07:27:02 GMT

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.

Re: header=no works for PC File Server?

hulllc — Thu, 03 Mar 2016 15:06:47 GMT

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.

Re: header=no works for PC File Server?

hulllc — Thu, 03 Mar 2016 15:27:19 GMT

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;