SAS® Access to Microsoft® Excel and CSV Files

Training Videos:

Below is a collection of SAS® papers and links for processing Excel and CSV files. See External files and Databases.

One of the advantages of using CSV file over excel is that column names of two or more words will automatically have an '_' between the words such as variable_name. Excel keeps the spaces between the words in the variable names such as 'variable name'n for numeric variables unless the VALIDNAME=V7 option is set.

Note that this notation is also acceptable as valid variable names within macro calls. In general, when needing to create a SAS dataset in UNIX, one of the best methods is to first create a CSV file and use DATA step to read the the CSV file. See Reading and Writing to Excel files training session. See SAS ODS Excel Tip Sheet, SAS on the PC and SAS reference sheet.

General Advantage/Disadvantages of using PROC IMPORT over CSV

PROC IMPORT Method	CSV and DATA Step or PROC IMPORT Method
Numeric values are treated as character vars	Numeric values are treated as numeric vars
Date values as 20150101 treated as character vars	Date values as 20150101 treated as numeric non-date vars
Date values as 01/01/2015 treated as numeric date vars	Date values as 01/01/2015 treated as numeric date vars
May have longer length of character variables even if length is less for first few rows	May reduce truncation of character variables if length is less for first few rows
Special characters such as : or , is better converted instead of being reduced to smaller length	Special characters such as : or , may cause character length to be smaller which may truncate values

Be aware of most common data issues when converting - Best to help resolve using PROC IMPORT: Mixed data type in column, variable type/length, dates, missing values, number of variables/records and header row

TIP: With respect to dates, it’s helpful to ensure that each date column in excel is formatted properly and consistent prior to initiating an import into SAS. If even one of the dates is in a different format in excel, SAS may be unable to resolve the appropriate format for that particular date, thus rendering that variable as missing or, at worst, affecting the entire variable post-import. In general, SAS trys to preserve the incoming date values, so that dates are read as a character string (date_c) and then transform to a date (input(date_c, format.). It’s not unusual to have a lot of records where date is missing but date_c is not, for reasons that include spaces or nonnumerics within the value to dates that just don’t convert, like December 45, 2012.

Below are several options to read/write excel files.

Option 1 - LIBNAME statements to read Excel files

How to specify an Excel worksheet name using the LIBNAME Excel engine statement

libname myxls "C:\My Documents\MyFile.xls";
proc print data=myxls.'Sheet1$'n; run;

data new;
set myxls.'Sheet1$'n;
run;

* Excel on PC;

LIBNAME myxls PCFILES SERVER=xxxxx PORT=8621 PATH="c:\demo.xls"; * default port V9.2;

*Libname to read XLSX files in SAS 9.4;

libname xl XLSX '/folders/myfolders/sas_tech_talks_15.xlsx';

**-- Using Excel LIBNAME Engine to create an excel file --**;

libname outxls excel 'example_out2.xls' version=2000 ;

data outxls.simple;

set test1; run;

data outxls.compute;

set test1; z = x + y; run;

Option 2 - PROC IMPORT to read Excel and CSV files (Requires SAS/Access to PC)

* imports more than 255 vars;

proc import out=qcdata.qc_original

datafile="C:\Users\my_file.xlsx"

dbms=XLSX replace;

sheet="MyData";

getnames=yes;

run;

PROC IMPORT OUT= WORK.&domain.1

DATAFILE= "&_vdtpath.\&domain..xls"

DBMS=XLS replace;

sheet='mysheet';

RUN;

proc import out=__temp_csv

datafile= "&csvpath.\&csvname..csv"

dbms=CSV replace;

guessingrows=1000;

getname=YES;

datarow=2;

run;

Option 3 - DATA Step to read CSV files (Delimiter Separated Data -DSD, useful method to create dataset in Unix)

'c:\sasconf\sascon07.csv'
04/16/07,04/19/07,"SAS Global Forum","Orlando FL"
06/03/07,06/06/07,"PharmaSUG","Denver CO"
09/16/07,09/18/07,"PNWSUG","Seattle WA"
09/30/07,10/02/07,"SCSUG","Austin TX"
10/17/07,10/19/07,"WUSS","San Francisco CA"
10/28/07,10/30/07,"MWSUG","Des Moines IA"
11/04/07,11/06/07,"SESUG","Hilton Head SC"
11/11/07,11/14/07,"NESUG","Baltimore MD"

DATA SAS_Conf07;
INFORMAT Start_Dt End_Dt mmddyy8. ConfName ConfLoc $CHAR16.;
FORMAT Start_Dt End_Dt date7.;

INFILE 'c:\sasconf\sascon07.csv' DSD;

INPUT Start_Dt End_Dt ConfName ConfLoc;
RUN;

Option 4 - ODS HTML to create Excel file from SAS Dataset

ODS HTML FILE=”C:\TEMP.XLS”;
PROC PRINT DATA=SASHELP.CLASS;
RUN;
ODS HTML CLOSE;

ODS MOFFICE2K FILE=”C:\TEMP.XLS”;
PROC PRINT DATA=SASHELP.CLASS;
RUN;
ODS MOFFICE2K CLOSE;

ODS MARKUP TAGSET=EXCELXP FILE=”C:\TEMP.XLS”;
PROC PRINT DATA=SASHELP.CLASS;
RUN;
ODS MARKUP CLOSE;

ods excel file='e:/PROCMEANS.xlsx' ;

proc means data=sashelp.cars ;

class type ;

run ;

ods excel close ;

Option 5 - ExcelXP Tagset to create custom Excel files

Useful to prevent error message when opening excel file.

ods listing close; /* Turn off SAS Listing off */
ods tagsets.excelxp path='C:\myfolder' file='multitable.xml' style=statistical
options(sheet_interval='none' sheet_name='Canada');

* Tagsets.excelxp options(embedded_titles='yes' embedded_footnotes='yes' sheet_internval='none|bygroup' sheet_name='My Sheet' absolute_column_width='9' orientation='landscape' fittopage='yes', skip_space='3,2,1' supress_bylines='no' contents='yes' index='yes' autofilter='all' width_fudge='0.8');

/* Two SAS Procedures in one sheet */
proc report; where country = 'Canada'; run; proc print; run;

ods tagsets.excelxp options(sheet_interval='none' sheet_name='Germany');

/* Second sheet */
proc report; where country = 'Germany'; run; proc print; run;

ods tagsets.excelxp close;
ods listing; /* Turn SAS Listing on */

Option 6 - ODS CSVALL Destination to create csv files

ODS CSVALL file='c:\temp\myreport.csv';
proc means data=sashelp.class min mean max median;
var height weight;
class sex;
‘Summary Statistics for SASHELP.CLASS’;
run;

ODS CSVALL CLOSE;

Note: In general, the best way to transfer data from Excel to SAS depends a lot upon the SAS version, the Excel version, The O/S, and the Hardware and if you have SAS/Access for PC Files.

Option 7 - Bridging the Barrier between Windows and Unix to create excel file with links

* Dataset of titles and rtf files;
* Note that rtf files must be in the same folder as the final TOC.xml file; * Best not to have spaces in title and filename;

data toc;
input title $char17. filename $40.;
cards;
example1 v1_stats_eff_series.rtf
example2 v1_stats_eff_corr_phe_chg.rtf
;
run;
proc print;run;

ods _all_ close;

* PC filename reference to store TOC excel file;
*ods tagsets.ExcelXP file='C:\your-directory\TOC\TOC.xml' style=Printer;

* Unix filename reference to store TOC excel file;
ods tagsets.ExcelXP file='/ace/acever/kuvan/pku/pku016/csr/output/stat/fig/TOC.xml' style=Printer;

title; footnote;

proc report data=toc nowd;
column title filename;

compute filename;
* PC or Unix pre path;
* urlstring = '/acever/kuvan/pku/pku016/csr/output/stat/fig/' || strip(put(filename, $40.));

* No pre path is needed if the RTF files are in the same folder since it is automatically added to the filename;
urlstring = strip(put(filename, $40.));

call define('title', 'URL', urlstring);
call define('filename', 'URL', urlstring);
endcomp;
run; quit;

ods tagsets.ExcelXP close;

Option 8 - Read manifest data in sas - required excel file to be open to prevent errors;

filename data

dde "Excel|Z:\Kite_Pharma\Biomarker\ZUMA-1\data_checks\[KTE CYK Manifest 18JAN2016.xlsx]Manifest!R3C1:R92C6" notab;

data manifest;

infile data dlm='09'x dsd missover;

length SubjectID $20. URMC $20. Visit $50. Sample_Type $20. Collection_Date $20. Collection_Time $20.;

input SubjectID $ URMC $ Visit $ Sample_Type $ Collection_Date $ Collection_Time $;

run;

Option 9 - PROC EXPORT to create Excel file in batch mode - useful to replace excel files.

proc export data=dc_7_lbcyt

file='Z:\Kite_Pharma\biomarker\zuma-1\production\data_checks\dc_7_1.xls'