Import masive excel files to SAS

[Angel_Saenz]

Is there any method or process to import several excel files to sas using SAS Enterpise Guide?, I used the import data option but it does one by one and I need to import 60 files.

In the past it was easy because I used SAS PC and had direct local connection to the PC.

[DanielSantos]

Hi.

EGuide is just a visual interface for SAS/BASE and it allows you to use your own SAS code.

If you managed to do this before with SAS/BASE there shouldn't be a problem to do the same with SAS EG.

Daniel Santos @ www.cgd.pt

[Reeza]

Im guessing you're running on a server without a direct connection to your PC? 

If so, can you upload the Excel files to the server and import them from there? 

[Angel_Saenz]

Hi Reeza, and thanks for help me again, answering your questions:

Im guessing you're running on a server without a direct connection to your PC? YES

When I had saspc/sasbase I use this code:

PROC IMPORT OUT= SIMWO200910

DATAFILE= "C:\files\SIMWO200910.xls"

DBMS=EXCEL REPLACE;

RUN;

So was easy import al year change 200910 by 200911 200912...

But now I only have SAS EG so I cant do a PC conection directly

If so, can you upload the Excel files to the server and import them from there? 

Today a system person help me with an add-in to import a masive excel but they has excel format and when i try to use same code

PROC IMPORT OUT= SIMWO200910

DATAFILE= "/lib/SIMWO200910.xls"

DBMS=EXCEL REPLACE;

RUN;

this error message send me

ERROR: DBMS type EXCEL not valid for import.

[Peter_C]

Hi @Angel_Saenz

ask your SAS administrator how to use the SAS/PC Files Server.

With that you would use DMBS= EXCELCS for PROC IMPORT

(where I believe the CS is meant to represent "client server"

best of luck

[Reeza]

You need to upload the file to use code otherwise you're stuck using the import GUI tool 😞

[Tom · Accepted solution]

Don't use EXCEL engine/dbms as that requires access to Microsoft code.

Use XLS or XLSX depending on the format of the Excel file.  The options available for PROC IMPORT are slightly different, but should be fine for nicely formated Excel files.

PROC IMPORT OUT= SIMWO200910
  DATAFILE= "/lib/SIMWO200910.xls"
  DBMS=XLS REPLACE
;
RUN;

Or just use a LIBNAME .

LIBNAME IN XLS  "/lib/SIMWO200910.xls";
proc copy inlib=in outlib=work;
run;

[Angel_Saenz]

thank you Tom I think DBMS=EXCEL option include xls, but I ned use DBMS=XLS as you told me