Unix SAS download and export to Excel

Often it is necessary to download (copy file from Unix to P/C) a SAS library and export it to Excel for review by users outsize the SAS environment. Not only as the final report but also as a diagnostic.

The macro, Download_Export, with three required parameters and one optional makes it quick and easy. The following three positional parameters are as expected: P/C Folder, Unix Directory, and File_Name. The optional parameter, File_Out, is used to rename the file.

Diagnostic messages

The P/C Folder, Unix Directory, File_Name, File_Out and sheet name are listed in the SAS log in additon to the following Notes.

NOTE: BINARY download in progress from infile=FILE_IN to outfile=PCDIR(file_name.sas7bdat)

NOTE: The file PCDIR(file_name.sas7bdat) is file P/C folder\file_name.sas7bdat.

NOTE: Downloaded <number> records and <number> bytes.

NOTE: <number> bytes were transferred at <number> bytes/second.

NOTE: <number> records were written to the file P/C folder\file_name.sas7bdat.

The maximum record length was <number>. The minimum record length was<number>.

NOTE: Remote submit to LOCAL_PC complete.

NOTE: Libref IN was successfully assigned as follows: Engine: V9 Physical Name: P/C folder

NOTE: New file "P/C folder\file_name.XLS" will be created if the export process succeeds.

NOTE: "file_name" was successfully created.

SAS Fifth Dimension Macro-DOWNLOAD_EXPORT

%MACRO DOWNLOAD_EXPORT(FOLDER,DIRECTORY,FILE_NAME,FILE_OUT=&FILE_NAME) ;

Parameter Description
FOLDER The P/C folder where the file is to be sent.
DIRECTORY The Unix directory with the file to be downloaded
FILE_NAME File name. UP to 31 characters can be specified. The FILE_NAME macro variable is used to specify the input file, the SAS file created on the P/C, the EXCEL workbook created and the sheet name. The sheet name is up to 30 characters. When necessary thirty characters are used to form the sheet name.
FILE_OUT Optional parameter:FILE_OUT=New output file name

Example

Assumptions: This macro is executed after signing on to Unix to enable a Remote submit.

%MACRO DOWNLOAD_EXPORT(FOLDER,DIRECTORY,FILE_NAME,FILE_OUT=&FILE_NAME) ;
%* copy SAS file from Unix Directory to P/C FOLDER ;

%* and create an Excel file ;

%GLOBAL SHEET_NAME ;

%LET SHEET_NAME=&FILE_NAME ;

%IF %LENGTH(&FILE_NAME)>31 %THEN %LET SHEET_NAME=%SUBSTR(&FILE_NAME,1,31) ;

%put %str(--->sheet_name=&SHEET_NAME) ;

%* copy file from Unix Directory to P/C FOLDER ;

%* The name can be changed in the copy ;

%PUT %STR(----------->FOLDER=&FOLDER) ;

%PUT %STR(----------->DIRECTORY=&DIRECTORY) ;

%PUT %STR(----------->FILE_NAME=&FILE_NAME) ;

%PUT %STR(----------->FILE_OUT=&FILE_OUT) ;

%PUT %STR(----------->SHEET_NAME=&SHEET_NAME) ;

filename PcDir "&FOLDER" ;

%SYSLPUT DIRECTORY=%BQUOTE(&DIRECTORY) ;

%SYSLPUT FILE_NAME=%BQUOTE(&FILE_NAME) ;

%SYSLPUT FILE_OUT=%BQUOTE(&FILE_OUT) ;

RSUBMIT ;

filename FILE_IN "&DIRECTORY/&FILE_NAME..sas7bdat" ;

proc download binary infile=FILE_IN

outfile=PcDir("&FILE_OUT..sas7bdat") ;

run ;

ENDRSUBMIT ;

RUN ;

LIBNAME IN "&FOLDER" ;

RUN ;

PROC EXPORT DATA= IN.&FILE_OUT

OUTFILE= "&FOLDER\&FILE_OUT" ;

DBMS=EXCEL REPLACE ;

SHEET="&SHEET_NAME" ;

%MEND DOWNLOAD_EXPORT ;