BookmarkSubscribeRSS Feed
mitchymitch
Calcite | Level 5

Hi,

I have an excel file that has 1 spreadsheet.  That spreadsheet has multiple tables on the 1 spreadsheet.  These tables are seperated by 1 row each. 

How would I go about importing each of these tables into SAS as a seperate SAS table?

2 REPLIES 2
jakarman
Barite | Level 11

using libname?    How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy


If the first sheet has been set up with many columns/ranges.

Name the ranges in SAS

5444 - How to read an Excel range into SAS using PROC IMPORT (name them your self)

19827 - PROC IMPORT cannot use RANGE option when EXCEL spreadsheet has specialCharacter in Sheet nam... (care special chars)

Using excel named ranges  http://www2.sas.com/proceedings/sugi31/024-31.pdf

reading the columns and all by yourself in one datastep and gnereate the sas-tables with that. 

SAS/ACCESS(R) 9.3 Interface to PC Files: Reference (getnames=no)

All depends on more not metnioned requirements/choices 

---->-- ja karman --<-----
Scott_Mitchell
Quartz | Level 8

Hi Mitchy,

As has mentioned there are many approaches you can take in this situation.

A simple method is using EXCEL Ranges and PROC COPY.  I am using PCFILE SERVER to access the EXCEL Workbook.  If you aren't using the PC FILE SERVER, you just need to change the LIBNAME statement to use your preferred method.  I would suggest that you maintain the acceptable SAS naming conventions when importing the data for named ranges, however if you are unable to do so you will need to use SAS name literals, which means enclosing your name in single quotes  followed by an N  e.g. 'SUM TOTAL'N.  I have other methods to correct for names that do not meet the SAS naming conventions, however they are more complicated and perhaps irreverent in your scenario.

The following link shows how to create a dynamic in EXCEL range to ensure that you are capturing all your data when additional rows are added.

How to create a dynamic defined range in an Excel worksheet

LIBNAME XLSFILE PCFILES TYPE=EXCEL PORT=8621 SERVER=LOCALHOST PATH = "E:\NEWMICROSOFTEXCELWORKSHEET.XLSX";

PROC COPY INLIB=XLSFILE OUTLIB=WORK; /*THE LIBRARIES WHERE THE DATA ARE PRESENTLY LOCATED - INLIB AND THE OUTPUT LIBRARY - OUTLIB */

  SELECT SAMPLERANGE TESTRANGE VARRANGE;/*THE NAMES OF  THE RANGES YOU WISH TO IMPORT - NAMES ARE RETAINED IN THE OUTLIB*/

RUN;

LIBNAME XLSFILE CLEAR;

There are more dynamic ways of importing all ranges, however given you will have to set up the ranges in EXCEL initially anyway, it is probably overkill in this situation.

Regards,

Scott

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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