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.
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.
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;
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;
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';
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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.