Help using Base SAS procedures

proc import for xlsm file

Reply
Frequent Contributor
Posts: 115

proc import for xlsm file

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

Super User
Posts: 5,257

Re: proc import for xlsm file

Frequent Contributor
Posts: 115

Re: proc import for xlsm file

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.

Super User
Super User
Posts: 7,407

Re: proc import for xlsm file

Are you using SAS University Edition?

Frequent Contributor
Posts: 115

Re: proc import for xlsm file

No, its SAS Unix

Super User
Super User
Posts: 7,407

Re: proc import for xlsm file

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.  

Community Manager
Posts: 2,763

Re: proc import for xlsm file

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.

 

New User
Posts: 1

Re: proc import for xlsm file

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

Ask a Question
Discussion stats
  • 7 replies
  • 3790 views
  • 1 like
  • 5 in conversation