Creating files for Excel in SAS can be done in different ways. One of the ways is to use the ODS tagsets.ExcelXP. The code below shows how to use the ODS tagsets.ExcelXP. It creates an XML-file that conforms to the Microsoft XML Spreadsheet Specifications and can be opened with Microsoft Excel 2002 (also known as Excel XP) or later versions.
Compared to other ways of creating Excel-files in SAS the ODS tagsets.ExcelXP is very diverse and has a lot of options for doing extra things in your Excel-sheets – like frozen headers etc.
NB! One downside of using ODS tagsets.ExcelXP is that is do not support graphics.
The code below manually creates two sheets in the Excel-file, populates them with data through PROC REPORT (eg PROC PRINT can also be used) and gives two columns (variables) a different color.
ods listing close;
/*
This prevents the open and save dialog from showing when the Excel-file
is finished processing. This has to be used if you automatically want to
convert the .XML file to .XLS or.XLSX.
*/
ods noresults;
ods tagsets.ExcelXP file="c:\output\ExcelFile.xml" style=Meadow
options(sheet_name="001_Sheet" autofilter='all'
frozen_headers='yes' width_fudge='.8' hidden_columns='');
proc report data=sashelp.class nowd;
columns _all_;
compute Age;
call define(_COL_, 'style', 'style=[background=LightGray]');
endcomp;
compute Height;
call define(_COL_, 'style', 'style=[background=LightGray]');
endcomp;
run;
ods tagsets.excelxp options(sheet_name=
"002_Sheet" autofilter='all' frozen_headers='yes' width_fudge='.8' hidden_columns='');
proc report data=sashelp.class nowd;
columns _all_;
run;
ods tagsets.excelxp close;
ods listing;
The Excel-XML files created can become very big, that’s why it’s sometimes a good idea to convert it to a native Excel-format like .XLS or .XLSX and still keep the changes made to the Excel-sheets. The macro below will convert the XML-files found in a folder to .XLS or .XLSX files using VBS (Visual Basic Script).
There can be a problem using the .VBS-script, if SAS is set to automatically start Excel to show the created file. The problem is that SAS is starting Excel to show the just created file, but also creates a lock on the file, that can collide with the .VBS-scrip trying to modify the file. This option for starting Excel can be removed in the SAS editor (Display Manager). Go to Tools -> Options -> Preferences -> Results (tab) and remove the tick from “View results as they are generated“. This will stop SAS from starting Excel when the .XML-file is created.
%
******************************************************************************************;
%*
%* MACRO: CONVERT_FILES
%*
%* USAGE: %convert_files(arg1=value, arg2=value, ... );
%*
%* DESCRIPTION:
%* This macro is used to convert files generated with ODS destinations
%* to native Excel files. For more information see the below link.
%* http://www.pharmasug.org/proceedings/2011/SAS/PharmaSUG-2011-SAS-TT02.pdf?page=12
%*
%*
%*
%*
%*******************************************************************************************;
options noxsync noxwait;
/* Example: %ConvertXML(Default=&SASProgramPath.\output\,
Store=&SASProgramPath.\output\temp.vbs); */
%macro ConvertXML(default=c:\temp\convert, store=c:\temp\, ext=xml);
data _null_;
file "&store";
put " Dim oXL ";
put " Dim oFolder";
put " Dim aFile";
put " Dim FSO";
put " Set oXL = CreateObject(""Excel.Application"")";
put " Set FSO = CreateObject(""Scripting.FileSystemObject"")";
put " oXL.DefaultFilePath = ""&default""";
put " oXL.DisplayAlerts = False";
put " if FSO.FolderExists(oXL.DefaultFilePath) then";
put " Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)";
put " For each aFile in oFolder.Files ";
put " If Right(LCase(aFile.Name), 4) = "".&ext"" Then";
put " oXL.Workbooks.Open(aFile.Name)";
put " oXL.Visible = false";
put " if (oXL.Version) >= 12 Then" ;
put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xlsx"",51";
put " Else";
put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xls"",-4143";
put " End If";
put " oXL.ActiveWorkBook.Close SaveChanges = True";
put " End If";
put " Next";
put " Set oFolder = Nothing";
put " end if";
put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
put " oXL.DisplayAlerts = True";
put " oXL.Quit";
put " Set oXL = Nothing";
call system("&store");
run;
%mend;
If you don’t want to remove the tick in “View results as they are generated” a little change in the program above is possible.
%
******************************************************************************************;
%*
%* MACRO: CONVERT_FILES
%*
%* USAGE: %convert_files(arg1=value, arg2=value, ... );
%*
%* DESCRIPTION:
%* This macro is used to convert files generated with ODS destinations
%* to native Excel files. For more information see the below link.
%* http://www.pharmasug.org/proceedings/2011/SAS/PharmaSUG-2011-SAS-TT02.pdf?page=12
%*
%*
%*
%*
%*******************************************************************************************;
options noxsync noxwait;
/* Example: %ConvertXML(Default=&SASProgramPath.\output\,
Store=&SASProgramPath.\output\temp.vbs); */
%macro ConvertXML(default=c:\temp\convert, store=c:\temp\, ext=xml);
data _null_;
file "&store";
put " Dim oXL ";
put " Dim oFolder";
put " Dim aFile";
put " Dim FSO";
put " Set oXL = CreateObject(""Excel.Application"")";
put " Set FSO = CreateObject(""Scripting.FileSystemObject"")";
put " oXL.DefaultFilePath = ""&default""";
put " oXL.DisplayAlerts = False";
put " if FSO.FolderExists(oXL.DefaultFilePath) then";
put " Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)";
put " For each aFile in oFolder.Files ";
put " If Right(LCase(aFile.Name), 4) = "".&ext"" Then";
put " oXL.Workbooks.Open(aFile.Name)";
put " oXL.Visible = false";
put " if (oXL.Version) >= 12 Then" ;
put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xlsx"",51";
put " Else";
put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xls"",-4143";
put " End If";
put " oXL.ActiveWorkBook.Close SaveChanges = True";
put " End If";
put " Next";
put " Set oFolder = Nothing";
put " end if";
put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
put " oXL.DisplayAlerts = True";
put " oXL.Quit";
put " Set oXL = Nothing";
*call system("&store");
run;
x "cscript ""&store""";
%mend;
You replace the call system with and x-command. If you use this option the XML-files created will still be locked and can therefore not be deleted. But it is possible to create the .XLS or.XLSX file.