This SAS KB article explains how to write SAS data into a Microsoft Excel range via Windows and UNIX/Linux.

SAS/ACCESS^® Interface to PC Files: Windows vs UNIX/Linux

You can use SAS/ACCESS Interface to PC Files to write data to Microsoft Excel. However, you must distinguish whether the SAS server/session runs on Windows or UNIX/Linux. SAS/ACCESS Interface to PC Files on UNIX/Linux has limited functionality compared to Windows.

• SAS/ACCESS Interface to PC Files on Windows offers the XLSX, XLS, and EXCEL engines.
• SAS/ACCESS Interface to PC Files on UNIX/Linux offers the XLSX and XLS engines. 

To be able to write to a Microsoft Excel range, you need to use the EXCEL engine, which is not available on UNIX/Linux.

The Microsoft Excel File

Marked by the area in green is a named-range “ClassRange”. In this scenario, you want to write data to this range.

SAS Code to Write in a Range of a Microsoft Excel Sheet on Windows

On Windows, you can use the EXCEL engine of SAS/ACCESS Interface to PC Files.

/* on WINDOWS you can use the EXCEL engine to write to a  */

/* Range in Excel                                         */

libname x EXCEL "c:\temp\RangeTest.xlsx" SCAN_TEXT=NO;

/* if you want to replace existing data in the Excel Range*/
/* you have to EMPTY the range first before you can write */
/* to it again                                            */
/* if you want to expand the Excel range with data, simply*/
/* comment this proc datasets step                        */

proc datasets lib=x noprint;

  delete ClassRange;

quit;

/* create test dataset----------------------------------*/

data BIG_Class;

   set sashelp.class;

run;

/* append test dataset to range-------------------------*/

proc append base=x.ClassRange

            data=Work.Big_Class;

run;

/* de-allocate library to the xlsx-file-----------------*/

libname x clear;

Data is written to the range, and the range is enlarged.

The SAS PC Files Server

Abstract

The SAS PC Files server is designed to overcome the limited functionality of SAS/ACCESS Interface to PC Files on UNIX/Linux and the 32-/64-bitness gap on Windows if Microsoft Office and SAS do not have the same bitness.

The SAS PC Files server runs on a Windows computer, either as a Windows service or in interactive mode. For information about how to install the SAS PC Files server, see the documentation below.

SAS PC Files Server as a Windows Service:

 

SAS PC Files Server as an interactive application:

Documentation

• SAS^® 9.4 PC Files Server: Installation and Configuration Guide, Second Edition
• SAS/ACCESS^® 9.4 Interface to PC Files: Reference, Fourth Edition

SAS Code to write in a Range of a Microsoft Excel Sheet on UNIX/Linux

You can use the PCFILES engine of SAS/ACCESS Interface to PC Files to connect to a SAS PC Files Server running on a Windows computer.

1. Start SAS Enterprise Guide and connect to your UNIX/Linux server.
2. Use this code example. Note that you need to change the LIBNAME statement according to your environment.

Server = DNS-Name of your Windows Computer on which SAS PC Files Server is running.
Path = Path to your XLSX File on your Windows Computer.
User = YourWindowsDomain\YourWindowsUserid
PW = YourWindowsPasword

/* the SAS PC Files Server must be installed and running--*/

/* on a Windows Computer                                  */

/* Libname statement using the PCFILES engine-------------*/

libname x PCFILES

          /* DNS name of the Windows computer    */

          server="YourWindowsComputer"    

            

          /* path to the XLSX file on Windows PC */

          path  ="c:\temp\RangeTest.xlsx" 

            

          /* YourWindowsDomain\YourWindowsUserId */

          user  ="YourDomain\YourUserID"  

            

          /* Windows password                    */

          PW    ="YourSecretPassword";    

/* if you want to replace existing data in the Excel Range*/

/* you have to EMPTY the range first before you can write */

/* to it again                                            */

/* if you want to expand the Excel range with data, simply*/

/* comment this proc datasets step                        */

proc datasets lib=x noprint;

  delete ClassRange;

quit;

/* create test dataset------------------------------------*/

data BIG_Class;

   set sashelp.class;

   age=age*10;

run;

/* append test dataset to range---------------------------*/

proc append base=x.ClassRange

            data=Work.Big_Class;

run;

/* de-allocate library to the xlsx-file-------------------*/

libname x clear;