01-06-2014 03:31 PM
I have this unusual need to be able to turn a SAS data set into an Excel file (either .xls or .xlsx is fine), but all the ways I have tried produce an Excel file with the first row containing either the SAS variable names or the SAS variable labels. I don't want this first row of the Excel file to contain the SAS variable names or variable labels. I want the first row of the Excel file to contain the first row of data in the SAS data set, and the 2nd row of the Excel file to contain the second row of data in the SAS data set, and so on.
How can I do this?
01-06-2014 08:00 PM
If you use ODS and PROC REPORT with the NOHEADER option, this is possible. You can create a CSV file to start and then switch to other destinations as needed/wanted (primarily if you want style information).
ods csv file='c:\temp\nolabels.csv';
proc report data=sashelp.class nowd noheader;
column name age sex height weight;
ods csv close;
01-07-2014 07:53 AM
Cynthia, a CSV file will not work here, as then I have to, within the program, convert CSV to Excel, and I don't know how to do that
Tom, can you give an example, as I couldn't get this to work using OSD TAGSETS.EXCELXP, and furthermore it creates an .xml file which creates the same issue for me, I don't know how to convert to XLS or XLSX
01-07-2014 08:19 AM
What is your OS? If you're on windows you could use PROC EXPORT then use VBSCRIPT or other scripting language to open the XLSX, delete the first row and save it. I've done similar in the past and there are some examples around of similar edits but I no longer have PC SAS so my usefulness is nil.
01-07-2014 08:25 AM
I am using Windows 7. I have no experience with VBSCRIPT or other scripting language, which is why I would like to do this entirely in SAS if possible.
01-07-2014 10:26 AM
An example that probably provides exactly what you are looking for can be found at:
01-07-2014 12:29 PM
Now I do remember using these techniques in the paper by Vyverman in the past, and at that time, with different needs, I discovered that ODS TAGSETS.EXCELXP was a better way to go. Furthermore, I also remember that something about the techniques in the paper by Vyverman had broken (I don't recall exactly what broke, or exactly what broke it, might have been Sas 9 or Office 2010) and I feel somewhat uncomfortable using techniques which Vyverman says were "developed, tested, and approved of on a Windows NT4 (SP6) system, running release 8.2 of the SAS System and MS Office97."
That doesn't mean I have ruled out the methods in Vyverman's paper, but when I investigate the entirety of X4ML commands, I do not see anything resembling a DELETE ROWS command, under any name that I can think of.
So that brings us back to ODS TAGSETS.EXCELXP, which creates an XML file, and thanks to , I believe I can now convert these XML files to XLSX, and using Cynthia's NOHEADER option in PROC REPORT, I think that solves the problem. At least, its the step I'm going to try next, but it seems like I can meet all requirements. I will let you know if it works.
01-07-2014 11:22 AM
This macro will create the VB code for you to convert a CSV or XML to native Excel File.
01-07-2014 02:56 PM
: Just in case you run into problems with the tagsets approach, below is a variant of a method that I and some others presented at the last MWSUG meeting (p.s. and will be presenting, again, at this year's SGF; see:
It'a an approach that uses proc fcmp to copy a sas dataset, and then creates and runs a vbs script to paste the data into an Excel workbook and, finally, saves the workbook. The lines I commented out would have copied the variable names:
proc fcmp outlib=work.func.util;
function c2cb(lib $,mem $);
if rc ne 0 then return(1);
if fid eq 0 then do;
rc = filename( 'clippy' );
if dsid eq 0 then do;
if rc ne 1 then do;
array v /nosymbols;
do i = 1 to nvar;
/* if i gt 1 then rc=fput(fid,'09'x);*/
do i=1 to attrn(dsid,'nlobs');
do j=1 to nvar;
if j gt 1 then rc=fput(fid,'09'x);
if missing(fmt) then fmt='best12.';
rc=fput(fid,putc(putn(getvarn(dsid,j ),fmt ),'$char12.'));
%put %sysfunc(c2cb(&lib,&mem)) ;
length script filevar $256;
script = catx('\',pathname('WORK'),'PasteIt.vbs');
filevar = script;
file dummy1 filevar=filevar recfm=v lrecl=512;
put 'Dim objExcel';
put 'Dim Newbook';
put 'Set objExcel = CreateObject("Excel.Application")';
put 'Set Newbook = objExcel.Workbooks.Add()';
put 'objExcel.Visible = True';
put 'objExcel = Nothing';
put 'Newbook = Nothing';