BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Angel_Saenz
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
DanielSantos
Barite | Level 11

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
Super User

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
Quartz | Level 8

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
Rhodochrosite | Level 12

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
Super User

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

Tom
Super User Tom
Super User

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
Quartz | Level 8
thank you Tom I think DBMS=EXCEL option include xls, but I ned use DBMS=XLS as you told me

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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