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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
Reeza
Super User

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;
HannahPhD
Obsidian | Level 7

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;

Reeza
Super User

Note the exact code may vary comment 🙂

 

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';
HannahPhD
Obsidian | Level 7

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 

Reeza
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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
  • 6 replies
  • 1408 views
  • 0 likes
  • 3 in conversation