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.

SAS For Dummies 3rd Edition! Check out the new edition, covering SAS 9.4, SAS Viya, and all of the modern ways to use SAS!
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;

 

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 3086 views
  • 0 likes
  • 4 in conversation