topic Import masive excel files to SAS in SAS Programming

Import masive excel files to SAS

Angel_Saenz — Thu, 09 Feb 2017 01:16:21 GMT

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.

Re: Import masive excel files to SAS

DanielSantos — Thu, 09 Feb 2017 08:05:46 GMT

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

Re: Import masive excel files to SAS

Reeza — Thu, 09 Feb 2017 08:14:24 GMT

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?

Re: Import masive excel files to SAS

Angel_Saenz — Mon, 13 Feb 2017 16:53:58 GMT

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.

Re: Import masive excel files to SAS

Peter_C — Mon, 13 Feb 2017 17:48:10 GMT

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

Re: Import masive excel files to SAS

Reeza — Mon, 13 Feb 2017 18:40:23 GMT

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

Re: Import masive excel files to SAS

Tom — Mon, 13 Feb 2017 18:43:41 GMT

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;

Re: Import masive excel files to SAS

Angel_Saenz — Mon, 13 Feb 2017 19:04:22 GMT

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