DATA Step, Macro, Functions and more

What is the most efficient way to import an excel file with multiple sheets to SAS?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

What is the most efficient way to import an excel file with multiple sheets to SAS?

Hi Everybody!

 

I have an excel file with 32 tabs(sheets) each containing data for a quarter. Each tab has the same number of variables, company names and brand IDs. What is the most efficient way to import all of this to sas a? I tried VBA but for some reason I only get 20 of my tabs combined not the 32 of them. I'd appreciate your comment and suggestions. 

 Thank you. 


Accepted Solutions
Solution
‎03-13-2016 11:19 PM
Super User
Super User
Posts: 7,955

Re: What is the most efficient way to import an excel file with multiple sheets to SAS?

Posted in reply to HannahPhD

The most efficient way to transfer data from one vendor to a client is to not use Excel in the first place.  It is unstructured - i.e. columns do not need to match, rows do not need to contain data, elements vary and things get hidden.  Use a proper data transfer method CSV, XML, delimited, database etc. Create an import agreement which both helps understand what the data is and what it contains, but also means that changes are highlighted as out of scope work, and programming becomes simple - as it just copies the agreement.  A robust, validatable process is the most efficient way to do anything computer related.

View solution in original post


All Replies
Super User
Posts: 19,815

Re: What is the most efficient way to import an excel file with multiple sheets to SAS?

Posted in reply to HannahPhD

Use the libname method and proc copy, assuming a decent naming structure that is SAS compliant.

 

libname test pcfiles path="path to my excelfile"; /* the exact code here may vary*/

proc copy in=test out=work;
run;
Occasional Contributor
Posts: 17

Re: What is the most efficient way to import an excel file with multiple sheets to SAS?

Thanks. but, when I ran your code I get these errors:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified
ERROR: Error in the LIBNAME statement.

 

this is my codes:

libname test pcfiles path="C:\Users\Hannah\Desktop\Literature\bavdata2.xls"; /* the exact code here may vary*/

proc copy in=test out=work;
run;

Super User
Posts: 19,815

Re: What is the most efficient way to import an excel file with multiple sheets to SAS?

Posted in reply to HannahPhD

Note the exact code may vary comment Smiley Happy

 

How you connect to files depends on your system. What version of SAS are you using? Try the XLSX or ExcelCS or Excel engines instead of pcfiles

 

libname mylib XLSX 'path to excel file';
Occasional Contributor
Posts: 17

Re: What is the most efficient way to import an excel file with multiple sheets to SAS?

I changed the libname but the problem is the new library keeps disappearing and I get the same error! Do you have any idea why it's happening? libname statement keeps failing to when I'm trying to assign the library with syntax too. 

 

My sas version is SAS (r) Proprietary Software 9.4 (TS1M1). 

 

Thanks 

Super User
Posts: 19,815

Re: What is the most efficient way to import an excel file with multiple sheets to SAS?

Posted in reply to HannahPhD

What do you mean by syntax too? Are you not programming?

 

Can you ask a colleague on how to set up the libname?

 

It should be simply the following:

 

libname my_file Excel 'path to file.xlsx';

Obviously this assumes you have SAS Access to PC Files licensed and you're not using SAS Studio or SAS UE or a unix server.

Solution
‎03-13-2016 11:19 PM
Super User
Super User
Posts: 7,955

Re: What is the most efficient way to import an excel file with multiple sheets to SAS?

Posted in reply to HannahPhD

The most efficient way to transfer data from one vendor to a client is to not use Excel in the first place.  It is unstructured - i.e. columns do not need to match, rows do not need to contain data, elements vary and things get hidden.  Use a proper data transfer method CSV, XML, delimited, database etc. Create an import agreement which both helps understand what the data is and what it contains, but also means that changes are highlighted as out of scope work, and programming becomes simple - as it just copies the agreement.  A robust, validatable process is the most efficient way to do anything computer related.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 373 views
  • 0 likes
  • 3 in conversation