Many specifically ask for spreadsheet data so that it may be manipulated further. A spreadsheet must be differentiated from a database and both importing and exporting data does not exactly deliver the most optimized and accurate results.
 |
Nevertheless with SAS you can both read from MS Excel in a variety of ways and also export data into spreadsheets to be opened in Excel. There are a number of methods to achieve the same each having its own set of disadvantages and advantages. Some of the better methods for doing the same follow in the paragraphs below.
Differences between 64-bit and 32-bit Architectures
A few of the concepts and terms need to be cleared out before we proceed.
For you to be able to create excel files from the SAS environment itself you are in need of SAS/ACCESS to PC Files. This will; let you perform all sorts of interchanges in file formats to and from SAS and other apps of the PC which include Excel as well.
But the shift from 32 bit architecture to a 64 bit one has ensured that 64 bit SAS sessions will not be able to run the PROC EXPORT DBMS=EXCEL program unlike its 32 bit counterpart.
This may be remedied in two different ways:
- Installing data providers which are 64 bit which ship with the 64 bit MS Office but keep in mind that you cannot have 32 bit and 64 bit variants of these data providers concurrently.
- You may also make use of the PC Files Server which resides on the same Windows installation where you have SAS. But for this to work it is necessary to have the SAS program use DBMS=EXCELCS which basically instructs SAS to utilize PC Files Server in contrast to data providers that are used in-process.
The Better Ways to Export SAS to Excel
- LIBNAME EXCEL- This serves to read and write Excel files as sheets when the bit architecture (i.e. 32 or 64 bit) of SAS matches that of MS Office. The excel file serves as a SAS library where ranges or sheets form the member tables.
- LIBNAME PCFILES performs the same function as the above with the difference being in using PC Files Server. It is good for situations where there is difference in architecture(Whether 32 bit or 64 bit)
- PROC EXPORT DBMS=EXCELCS makes use of PC Files Server to write files in the Excel format. The output behavior may be controlled to a great extent and is well suited to situations where there is a difference in bitness.
- PROC EXPORT DBMS=EXCEL- This is most suitable in cases where the bit architecture of SAS matches that of MS Office.
- PROC EXPORT DBMS=XLS- This lets you export to Excel files directly without any external dependencies. However its function are limited as far as format and volume are concerned. It works on both the UNIX and Windows platforms.
- PROC EXPORT DBMS=XLSX- This was introduced in version 9.3M1 and is able to directly export to Excel 2010 files. There is no need for any driver or added software like PC Files Server.
The methods listed next do not have SAS/ACCESS to PC Files as a pre-requisite and are hence are quite popular in spite of the fact that they do not output “native” Excel files.
- PROC EXPORT DBMS=CSV- This produces CSV files used most often in Excel.
- ODS TAGSETS.CSV- This also creates CSV files. An alternative way is to use the DATA step and FILE output.
- ODS TAGSETS.EXCELXP- This makes use of ODS to output XML files in Office. The appearance of the content may be controlled to a significant extent but recent MS Excel versions refuse to see it as a “native” format.
- FILENAME DDE- This uses Windows messages in order to control the functions of Excel files down to the level of cells. In order for this to work SAS and Excel must reside in the on the same machine. Another point to be noted is that it will not work in Workspace Servers of SAS or servers which are referred to as stored process. It lets you leverage a tremendous amount of control, a quality prized by Advanced SAS users.
The SAS Add-in for MS Office resolves the problem rather easily. It allows you to access data related to SAS and their analytics right from the Excel environment and session.
If all this sounds exciting to you even if you are a newbie to the field a proper SAS Training Institute in Gurgaon will help you along the way.
If all this sounds exciting to you even if you are a newbie to the field a proper SAS Training Institute in Gurgaon will help you along the way.
Related posts :
You write this post very carefully I think, which is easily understand to me. Not only this, other post is also good. As a newbie this info is really helpful for me. I think people should go through it. Thanks to you. if you looking for the best SAS Training Institute in Delhi So Contact Us-9311002620 Or Visit Our Website-https://www.htsindia.com/Courses/business-analytics/sas-training-institute-in-delhi
ReplyDelete