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

I am trying to import data from Excel into SAS/STAT.  However when I use the pulldown meu option, there is no option for me to import an .xlsx file.  Attached is a screenshot.  

 

Please advise how to proceed.  Thanks.

Jack

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Or save the data to a CSV file and import that. If you have multiple files of the same structure you may want to copy the datastep code created when importing a CSV to save and edit. Otherwise multiple imports of Excel have a nasty habit of changing variable characteristics from file to file.

View solution in original post

6 REPLIES 6
ChrisHemedinger
Community Manager

In Base SAS (the interface you're using -- SAS Display Manager or "PC SAS") - you need SAS/ACCESS to PC Files to be licensed/installed in order to import Excel files.

 

Do you have SAS Enterprise Guide available?  (Check your Programs->SAS folder).  If so, you can use that to import Excel files into a data set, no SAS/ACCESS needed.

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
Jack1
Obsidian | Level 7

Hi,

 

Thanks for quick response.  Yes, I have SAS Enterprise Guide on my computer and have used it to import Excel files (and at this time we do not have the SAS/ACCESS to PC files add-in).  However the Excel file I want to read data from has 12 tabs and EG seems a bit cumbersume for the task.  Or is it?  I am new to working with SAS and any advice is greatly appreciated.

 

Thanks

Jack

ballardw
Super User

Or save the data to a CSV file and import that. If you have multiple files of the same structure you may want to copy the datastep code created when importing a CSV to save and edit. Otherwise multiple imports of Excel have a nasty habit of changing variable characteristics from file to file.

nketata
Obsidian | Level 7

Try this

 

libname HELLO pcfiles path = "C:\myfile.xlsx" textsize = 32767 ;

DATA WANT;

set HAVE.'SHEET1$'n;

run; libname HELLO clear;

Jack1
Obsidian | Level 7

Ok, coding appears a lot faster vs. manually importing since my spreadsheet has 12 tabs I need to import.

 

So here is the structure of my spreadsheet:

 

* Each tab is named for each month of the year (Jan, Feb, through to Dec); 12 tabs total

* The column names start in row 12, with columns of data running from A to S; this is consistent accross all 12 tabs of data.  Information in rows 1-11 are not needed 

 

How would the code appear given these features of the data I need to get into a SAS dataset?  Please advise....

 

Thanks...

nketata
Obsidian | Level 7

You run this command for each sheet, each sheet at a time.

You can add sas commands in the data/set the way you want. I added a rename as an example.

 

 

libname HELLO pcfiles path = "C:\myfile.xlsx" textsize = 32767 ;

DATA WANT (rename =(var1=alpha)) ;

set HAVE.'SHEET1$'n;

run; libname HELLO clear;

 

 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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