Help using Base SAS procedures

Importing data from Excel in SAS/STAT

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Importing data from Excel in SAS/STAT

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


Accepted Solutions
Solution
‎05-20-2016 12:44 PM
Super User
Posts: 10,490

Re: Importing data from Excel in SAS/STAT

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


All Replies
Community Manager
Posts: 2,761

Re: Importing data from Excel in SAS/STAT

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.

Contributor
Posts: 52

Re: Importing data from Excel in SAS/STAT

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

Solution
‎05-20-2016 12:44 PM
Super User
Posts: 10,490

Re: Importing data from Excel in SAS/STAT

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.

Contributor
Posts: 34

Re: Importing data from Excel in SAS/STAT

Try this

 

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

DATA WANT;

set HAVE.'SHEET1$'n;

run; libname HELLO clear;

Contributor
Posts: 52

Re: Importing data from Excel in SAS/STAT

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...

Contributor
Posts: 34

Re: Importing data from Excel in SAS/STAT

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;

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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