topic Re: Importing data from Excel in SAS/STAT in SAS Procedures

Importing data from Excel in SAS/STAT

Jack1 — Fri, 20 May 2016 16:21:05 GMT

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

Re: Importing data from Excel in SAS/STAT

ChrisHemedinger — Fri, 20 May 2016 16:24:16 GMT

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.

Re: Importing data from Excel in SAS/STAT

Jack1 — Fri, 20 May 2016 16:33:39 GMT

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

Re: Importing data from Excel in SAS/STAT

ballardw — Fri, 20 May 2016 16:33:47 GMT

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.

Re: Importing data from Excel in SAS/STAT

nketata — Fri, 20 May 2016 19:09:25 GMT

Try this

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

DATA WANT;

set HAVE.'SHEET1$'n;

run; libname HELLO clear;

Re: Importing data from Excel in SAS/STAT

Jack1 — Wed, 25 May 2016 17:59:20 GMT

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

Re: Importing data from Excel in SAS/STAT

nketata — Mon, 04 Jul 2016 18:06:18 GMT

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;