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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 2241 views
  • 0 likes
  • 4 in conversation