topic Re: proc import for xlsm file in SAS Procedures

proc import for xlsm file

vraj1 — Mon, 07 Mar 2016 12:11:47 GMT

i want to import .xlsm file using proc import.

when i did using the below code, i get the following error.

proc import
datafile="&workdir./progplan.xlsm"
out = import_progplan
dbms = excelcs replace;
sheet = "Programming Plan";
getnames=yes;
mixed=no;
run;

mixed=no; run;
_____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

ERROR: Statement or Option "GETNAMES" not valid for EXCELCS IMPORT.

Can anyone help me

Re: proc import for xlsm file

LinusH — Mon, 07 Mar 2016 12:20:20 GMT

http://support.sas.com/kb/41/060.html

Re: proc import for xlsm file

vraj1 — Mon, 07 Mar 2016 13:02:08 GMT

i added the below code but still get error.

proc import
datafile="&workdir./progplan.xlsm"
out = import_progplan
dbms = excelcs replace;
sheet = "Programming Plan";
range='test1$a2:e21'n;

run;

ERROR: Server Name is invalid or missing.

Re: proc import for xlsm file

RW9 — Mon, 07 Mar 2016 13:03:49 GMT

Are you using SAS University Edition?

Re: proc import for xlsm file

vraj1 — Mon, 07 Mar 2016 13:09:11 GMT

No, its SAS Unix

Re: proc import for xlsm file

ChrisHemedinger — Mon, 07 Mar 2016 13:12:31 GMT

EXCELCS requires the SAS/ACCESS to PC Files component "PC FIles Server". If you're running SAS for Windows, that should autostart -- though it might need some configuration. And you'll have to make sure it's installed.

On SAS for UNIX, you need to have PC Files Server on a Windows node in your network, then specify that node as part of the PROC IMPORT statement -- this is how Excel-specific behaviors can be delegated from SAS on UNIX to the Excel engine. Note that you don't need this in place for the more common XLSX files -- DBMS=XLSX works without it. But for macro-enabled XLSM files, you need to push the work to this specific Excel engine using EXCELCS and the PC Files Server.

Re: proc import for xlsm file

RW9 — Mon, 07 Mar 2016 13:28:23 GMT

Simplify your process. Save the "data" to a "data transfer" format e.g. CSV or XML. Then write a simple data step import program. This will then be robust, cross platform, easy to maintain etc. Excel is not a good data transfer format, it is proprietary (i.e. there is no Office for Unix) - so not cross platform, nor is it easy to read without that software, nor is it strcutured data.

Re: proc import for xlsm file

reddaniel — Fri, 28 Oct 2016 02:17:39 GMT

Hi, I'm stuck with a similar error. I'm trying to import data from a .XSLX. and while I execute the PROC IMPORT I get this error: "ERROR: Server Name is invalid or missing."

This file is placed in a shared folder with a path like this: \\file_server_name\folder\subfolder, I'm using SAS Enterprise Guide 7.1 and I guess we are using a UNIX server for the metadata because in the LOG of the import task the INFILE has this path: '/sasendwork/work/SAS_work722C01660016_sas_server_name/#LN00014'

I need to import files using code because I have to upload a daily report placed in this shared folder that has a date in the name, something like 'File mm-dd-yyy.xlsx'

I tried a lot of things but I guess most of all solutions are intended for SAS 9.1 or later versions.

Any help will be very appreciate since we expent a lot of time updating the import task everyday.

Regards,

-Daniel