02-10-2011 05:02 PM
10-20-2011 08:55 PM
If the ODS solution is not feasible, check to see if you have licensed SAS/Access to PC File Formats for your Unix box, and if so, verify that the the SAS administrator has installed and configured the PC File server that comes with the SAS/Access product on a Windows box on your network. With this infrastructure in place, you can read and write to Excel spreadsheets from your Unix SAS session using a LIBNAME statement. The LIBNAME statement would look something like this:
libname xlr PCFILES path="D:\folder\MyFile.xls" server=<server name> port=<port#>;
You would replace <serve name> with the name of your server (somethin like pcff.mydomain.com) and <port#> with the port number used to access the PC File server (the default value is 8621).
If you want to write data to specific cell ranges in the Excel spreadsheet, it is best to set up NAMED RANGES in the spreadsheet first. When you assign a libname to an Excel workbook that contains named ranges, each named range appears as a separate data set in the SAS library. Due to limitations of the MS Jet Engine, which SAS Access uses to read and write to Excel, you can't overwrite data in the existing cells - you first have to empty the range then write in the new data.
For example, say the workbook MyFile.xls contains a named range called "PutDataHere" and you want to fill it with the data from SASHELP.CLASS. The following code should do the trick:
libname xlr PCFILES path="D:\folder\MyFile.xls" server=pcff.mydomain.com port=8621; proc sql; drop table xls.PutDataHere; quit; data xls.PutDataHere; set sashelp.class; run;