Importing data from Excel in SAS/STAT

[Jack1]

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

[ChrisHemedinger]

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.

[Jack1]

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 · Accepted solution]

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]

Try this

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

DATA WANT;

set HAVE.'SHEET1$'n;

run; libname HELLO clear;

[Jack1]

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]

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;