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
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.
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.
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
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.
Try this
libname HELLO pcfiles path = "C:\myfile.xlsx" textsize = 32767 ;
DATA WANT;
set HAVE.'SHEET1$'n;
run; libname HELLO clear;
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...
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.