DATA Step, Macro, Functions and more

Import masive excel files to SAS

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Import masive excel files to SAS

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.


Accepted Solutions
Solution
‎02-13-2017 02:03 PM
Super User
Super User
Posts: 7,038

Re: Import masive excel files to SAS

Posted in reply to Angel_Saenz

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;

View solution in original post


All Replies
Super Contributor
Posts: 474

Re: Import masive excel files to SAS

Posted in reply to Angel_Saenz

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

 

Super User
Posts: 19,768

Re: Import masive excel files to SAS

Posted in reply to Angel_Saenz

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? 

Contributor
Posts: 43

Re: Import masive excel files to SAS

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.

Valued Guide
Posts: 2,177

Re: Import masive excel files to SAS

Posted in reply to Angel_Saenz

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

Super User
Posts: 19,768

Re: Import masive excel files to SAS

Posted in reply to Angel_Saenz

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

Solution
‎02-13-2017 02:03 PM
Super User
Super User
Posts: 7,038

Re: Import masive excel files to SAS

Posted in reply to Angel_Saenz

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;
Contributor
Posts: 43

Re: Import masive excel files to SAS

thank you Tom I think DBMS=EXCEL option include xls, but I ned use DBMS=XLS as you told me
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 220 views
  • 3 likes
  • 5 in conversation