SAS® Access to Microsoft® Excel and CSV Files    

          Training Videos:

Reading and Writing to Excel files


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

See also ODS, PROC IMPORTPROC EXPORT and SAS Enterprise Guide.

Microsoft Excel

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)

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;

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 - 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'

dbms=excel replace;

sheet='SDTM_LBCYT';

run;

______________________________________________

SAS® Guide ExcelXP Tagset Paper Index

SAS® ExcelXP Tagset Examples, Syntax

SAS® HOW, Guide An Introduction to Creating Multi-Sheet Microsoft Excel Workbooks the Easy Way with SAS, 

SAS® and Excel Presentation and Tools (1) (2)

SAS® Utility Gallery of Programs for ODS and Excel:

1. Workbook containing sheets by-group (SASHELP.CLASS Excel)

2. Workbook containing sheets for each dataset

3. Workbook of individual excel files

Using ODS to Generate Excel Files

SAS® Options Paper Tag It, Bag It, Put It Out into Excel

SAS® Webinar An Introduction to Creating Multi-Sheet Microsoft Excel Workbooks the Easy Way with SAS®

SAS® Paper SAS Add-In for Microsoft Office

SAS Add-In Product for Microsoft Office

SAS® Reference Microsoft Excel Workbook Files, Training Post Blog on CSV

Microsoft® Excel Tips and Tutorials    Mr Excel.com  TechNet XLS

Difference between CSV and Excel files

Stakana Analytics (Free SAS to Excel Download)

Member Pick 1. Don’t Gamble with Your Output: How to Use Microsoft Formats with ODS,
Cynthia Zender

Member Pick 2. Excellent Dating in SAS®, James Zeitler

______________________________________________

1. Moving Data and Analytical Results between SAS® and Microsoft® Office, Vincent DelGobbo

2. Generating Custom Excel Spreadsheets using ODS, Chevell Parker

3. PROC IMPORT with a Twist, Gary McQuown [DATAROW=, GETNAMES=]

4. You Use SAS®, Your Boss Uses Excel. Guess Where Your Results Are Going to Appear! Using ODS to Create Your Results in Excel, William E Benjamin

5. Changing that pesky datetime to a date, Stanley Fogleman

6. Revisting DDE: An Updated Macro for Exporting SAS Data into Custom-Formatted Excel Spreadsheet, Part I - Usage and Examples, Nat Derby

7. Revisting DDE: An Updated Macro for Exporting SAS Data into Custom-Formatted Excel Spreadsheet, Part II - Programming Details, Nat Derby

8. Multiple Multi-Sheet EXCEL Workbooks Using ODS, Wanda Mercer

9. Moving Data and Results Between SAS® and Microsoft Excel, Harry Droogendyk

10. Customized Excel Output Using the Excel Libname, Harry Droogendyk

11. Tying it Together: Using ODS to Insert EXCEL® Hyperlinks, James E. Snider, Garland D. (David) Maddox [Table of contents]

12. Using Macros and Spreadsheets in a Regulated Environment, Wolfgang Winter and Ludwig Huber

13. Maintaining Formats when Exporting Data from SAS® into Microsoft® Excel®
Nate Derby, Colleen McGahan
 [Missing values]

14. Getting Started with ODS: Generating Formatted Reports Using the ExcelXP Tagset, Allan Del Rosario, Jennifer Mefford

15. Accessing MICROSOFT EXCEL and MICROSOFT ACCESS Through the Use of a Simple Libname Statement, Kee Lee

16. A simple way to access the data in EXCEL through SAS v9/ ACCESS® libname and Excel engine, Lianbo Zhang [SCANTEXT]

17. Using ODS to Generate Excel Files Chevell Parker 

18. How to Excel with SAS, Keith Cranford [HOW]

19. Its not the Yellow Brick Road but the SAS PC FILES SERVER® will take you Down the LIBNAME PATH= to Using the 64-Bit Excel Workbooks, William Benjamin

20. Smart Import/Append Data in Excel Sheets, Zhengping Ma, Liping Liu

21. AN ANIMATED GUIDE: SENDING SAS FILE TO EXCEL, Russ Lavery

22. All Aboard! Next Stop is the Destination Excel, William Benjamin [9.4]

23. Choosing the Best Method to Create an Excel Report, Romain Miralles

24. Dating SAS® and MS Excel, Erik Tilanus

25. Using the New ODS EXCEL Destination in SAS® 9.4 When Working with Remote Servers, Tom Bugg

26. Bring Excel file with multiple sheets to SAS, Mindy Wang

27. A Poor/Rich SAS® User’s Proc Export, Arthur Tabachneck, Tom Abernathy, Matthew Kastin

28. Creating an Excel report: A comparison of the different techniques, Romain Miralles

29. Exporting Variable Labels as Column Headers in Excel using SAS, Chaitanya Chowdagam

30. So, Your Data are in Excel! Ed Heaton

31. Creating and Maintaining large Metadata EXCEL files John Adams

32. Reporting Non-Printable and Special Characters for Review in Excel, Abhinav Srivastva

33. ODS Destination for Excel, Top 10 FAQs, David Kelley


Dynamic Data Exchange (DDE)  Issues with SAS EG

1. Generating Custom Report Tables: Using SAS with DDE and VBA, Ying Feng

2. Automatically Generating Highly Customized Excel® Reports with SAS/Macro® and Integration Technologies (DDE), Helen Fu, Meghal Parikh, Elayne Reiss and Sandra Archer

3. Generating Reports with DDE and VBA, the Concept- Alternative to ODS to Produce Complex Reports, Chienchung Chen

4. Fancy MS Word Reports Made Easy: Harnessing the Power of Dynamic Data
Exchange — Against All ODS, Part II, William Viergever, Koen Vyverman

5. An Introduction to SAS® Applications of the Windows Scripting Host, Stephen Hunt, Tracy Sherman, Brian Fairfield-Carter

6. Let SAS® Tell Microsoft Word® to Collate, Haiping Luo

7. From SAS ASCII Output to Word Document – A SAS Macro Approach, Jianlin Zhou

8. Using Dynamic Data Exchange with Microsoft Word, Jodie Gilmore

9. Using Dynamic Data Exchange Within SAS® Software to Directly Access Data From Microsoft
Windows Applications, Ellen Asam, Donna Usavage

10. Reading and Writing Data from Microsoft Excel/Word Using DDE, Destiny

11. Importing Excel Files Into SAS Using DDE, Curtis Smith [Batch run to auto open Excel file]

12. Preparing Final Reports Using DDE to Link SAS Software and Microsoft WORD, Dean Bross

13. Using Dynamic Data Exchange and Table-Driven Programming to Manage Excel Files, Xiaoming Liang, Kim Musgrave

Powered by Wild Apricot Membership Software