We’re smarter together. Learn from this collection of community knowledge and add your expertise.

How to Import an Excel File to SAS University Edition

by Super Contributor on ‎04-02-2015 02:05 PM - edited on ‎10-05-2015 03:09 PM by Community Manager (9,273 Views)

One of the critical tasks in SAS is to take data from an outside source and bring it into the environment for analysis.  One of the most ubiquitous sources for data is, of course, Excel, so I decided to use it as the example for this “How to Import” article.

 

First and foremost, the Excel file must be in XLS (97 – 2004 file format) – the new XLSX extension won’t import with the automatically generated SAS code.  In the event that your spreadsheet is in XLSX format, you could change the DBMS= to XLXS and change the extension on the file to XLSX and it works fine.

 

Secondly, please read the documentation for the virtual machine / operating system – setting up the folder will literally “make or break” your ability to import data.

 

One of the features I’m extremely fond of in SAS University Edition are the “Snippets” (see https://communities.sas.com/docs/DOC-10596 for more information).  Using the IMPORT XLS snippet, and putting in the path to my file, this is what I end up with.

 

Easy and straightforward, and for a regular Excel file without anything unusual, this will work.

 

Screen Shot 2015-03-25 at 8.04.01 PM.png

 

Here are the results as outputted by SAS:

 

Screen Shot 2015-03-25 at 8.10.17 PM.png

But we all know that a data file is rarely perfect.  I’m going to show you a couple of tricks to get around some oddball stuff I’ve encountered.

 

Here’s a screenshot of the data (modified from the previous example’s data):

 

Screen Shot 2015-03-25 at 8.13.06 PM.png

 

Note that I’ve added line of text on the first row.  When I import this using the previous code, SAS has done what I asked – imported the complete dataset and printed it out.  It used the text from the first cell as the name of the first variable, and it generated names for the other rows, since the corresponding cells were blank in row 1.  Obviously this won’t work, and I need to figure out a way to import just the data.

 

Screen Shot 2015-03-25 at 8.17.30 PM.png

 

Using the DATAROW statement, which allows me to specify what row the data starts on, seems promising.

 

Screen Shot 2015-03-25 at 8.56.49 PM.png

 

OK, I’m getting really close – but still not quite.  I need some way to have SAS Studio assign valid names to the variables, instead of using the column headings from the worksheet.

 

Screen Shot 2015-03-25 at 8.20.50 PM.png

 

Here’s the code, using the GETNAMES=NO statement.

 

Screen Shot 2015-03-25 at 8.55.53 PM.png

 

That does it – the data is imported, and we can now get down to the business of our analysis!

 

Screen Shot 2015-03-25 at 8.21.07 PM.png

Comments
by Occasional Contributor josyestat
on ‎05-27-2015 11:08 PM

Hi!

I'm using the SAS University Edition in Ubuntu 14.04. I have dificult to import Excel file, CSV file  and TXT file to SAS University Edition. I looking your poster, I saw that you saved your Excel file in "myfolders" folder. This is folder was created on the moment the installation. Then, all files must be save in this folder to import file to SAS University Edition?


Maria

by Super Contributor
on ‎05-28-2015 10:13 AM

Hi Maria!

Thank you for your comment :-)  I don't know how Ubuntu folders work, but in the Mac, all the files have to be saved to the "myfolder" folder.  I would suggest reading the documentation for the Virtual Machine you're using to see where it recommends the files should be saved.

Good luck and post any questions back here!  Have a great day!

Chris

by Occasional Contributor josyestat
on ‎05-28-2015 10:54 AM

Thanks Chris,

I did what you suggested, but continue the same error. The results of the log and code window are follow,sas.png log.pngHave a great day for you too!

Maria

by Super Contributor
on ‎05-28-2015 01:56 PM

Hi Maria,

Sorry I couldn't reply sooner, I'm busy at work :-)

I see you posted your question and got a response from Cynthia.  She's saying basically the same thing but has provided screen shots - follow her steps and you should be OK.

Good luck!

Chris

by Occasional Contributor josyestat
on ‎05-28-2015 02:18 PM

Thanks Chris, no problem. I needed an answer urgently.

I did not understand what directory was used to import data.

With the response from Cynthia, I could see the problem, the code is working ok now.

Thanks,

Maria

by Super Contributor
on ‎05-28-2015 02:27 PM

Glad to hear everything is OK!  Good luck with everything else and I'll be posting more about SAS University Edition, so stay tuned!

Chris

by Occasional Contributor josyestat
on ‎05-28-2015 02:37 PM

It's  great !! I'll stay tuned, because I work with SAS and I maybe need of halp again.

Thanks,

Maria

Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.