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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
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-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
  • 6 replies
  • 1446 views
  • 0 likes
  • 4 in conversation