BookmarkSubscribeRSS Feed
helloagainoh2
Calcite | Level 5

Hello, I am wondering what is the advantage of using these two methods for importing excel datasheets and manipulating their data? I use SAS Enterprise Guide for my work.

 

I have been assigned to work on an annual project that will take in excel sheets a few times each year. Currently I have all of the excel sheets in a SAS Project and I used the file->import data option on SAS enterprise to store the code to transform the excel sheets into SAS datasets.

 

Should I instead use proc import code to pull in the excel sheets? 

4 REPLIES 4
PaigeMiller
Diamond | Level 26

Is there something wrong with doing this using a Project in Enterprise Guide that makes you want to switch? Please provide details.

 

 

--
Paige Miller
helloagainoh2
Calcite | Level 5

No, I am use to running it as a Project in SAS Enterprise Guide. I have a few co-workers who will be doing the maintenance and adding in new excel sheets, and are wanting to know why not use proc import instead. 

 

 

 

Kurt_Bremser
Super User

If your Enterprise Guide connects to a remote server (usually called SASApp OOTB), then the EG import task does several things for you:

  • use a Windows-based MS-supplied module to convert the Excel sheet to a specially formatted text file
  • upload that text file to your WORK directory on the server
  • submit code on the server to read that text file into a dataset

If you want to use PROC IMPORT or LIBNAME XLSX to read the spreadsheet, you need to move the file to the server first; you can do this with the Copy Files task in EG.

 

If your EG is part of a workstation setup (meaning it uses the Local server), then you can use the code options directly.

ballardw
Super User

@helloagainoh2 wrote:

Hello, I am wondering what is the advantage of using these two methods for importing excel datasheets and manipulating their data? I use SAS Enterprise Guide for my work.

 

I have been assigned to work on an annual project that will take in excel sheets a few times each year. Currently I have all of the excel sheets in a SAS Project and I used the file->import data option on SAS enterprise to store the code to transform the excel sheets into SAS datasets.

 

Should I instead use proc import code to pull in the excel sheets? 


Proc Import will make different guesses for each file as to the properties of the variables such as length and type based on a very few rows of data by default. Which means that your follow up steps are likely to have issues.

I am not sure that a "project" will do any better.

Since you say that you will read a "few files per year" I would suggest:

1) documenting what the data should be- variable names, types, layout in the source data, lengths and such

2) saving source files as CSV and writing a data step to read them according to the documentation you created

Then use the data step changing the input file name and output data set name as needed for each new file.

 

Having data consistent at the beginning will save a lot of headaches trying to "fix" things when you combine data for any analysis or reporting purpose. The documentation of what should be in the file makes it easier for someone else later to see what you have (or are supposed to have) to work with.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 455 views
  • 0 likes
  • 4 in conversation