topic Re: Want to create an Excel file without SAS variable names or variable labels in Row 1 in ODS and Base Reporting

Want to create an Excel file without SAS variable names or variable labels in Row 1

PaigeMiller — Mon, 06 Jan 2014 20:31:21 GMT

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?

Re: Want to create an Excel file without SAS variable names or variable labels in Row 1

Cynthia_sas — Tue, 07 Jan 2014 01:00:26 GMT

Hi:

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).

Cynthia

ods csv file='c:\temp\nolabels.csv';

proc report data=sashelp.class nowd noheader;

column name age sex height weight;

run;

ods csv close;

Re: Want to create an Excel file without SAS variable names or variable labels in Row 1

Tom — Tue, 07 Jan 2014 01:07:13 GMT

ods tagsets.excelxp will work with that also and is closer to a "real" excel file than a CSV file is.

Re: Want to create an Excel file without SAS variable names or variable labels in Row 1

PaigeMiller — Tue, 07 Jan 2014 12:53:26 GMT

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

Re: Want to create an Excel file without SAS variable names or variable labels in Row 1

data_null__ — Tue, 07 Jan 2014 13:19:47 GMT

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.

Re: Want to create an Excel file without SAS variable names or variable labels in Row 1

PaigeMiller — Tue, 07 Jan 2014 13:25:51 GMT

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.

Re: Want to create an Excel file without SAS variable names or variable labels in Row 1

art297 — Tue, 07 Jan 2014 15:26:41 GMT

: Given your other post this morning, regarding dde commands, in addition to the two references I posted in that thread, look at ALL of Koen Vyverman's papers or posts on SAS-L by Kilovolt.

An example that probably provides exactly what you are looking for can be found at:

http://www2.sas.com/proceedings/sugi27/p190-27.pdf

Re: Want to create an Excel file without SAS variable names or variable labels in Row 1

Reeza — Tue, 07 Jan 2014 16:22:23 GMT

43496 - Convert files created using an ODS destination to native Excel files

This macro will create the VB code for you to convert a CSV or XML to native Excel File.

Re: Want to create an Excel file without SAS variable names or variable labels in Row 1

PaigeMiller — Tue, 07 Jan 2014 17:29:38 GMT

, thank you very much.

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.

Re: Want to create an Excel file without SAS variable names or variable labels in Row 1

art297 — Tue, 07 Jan 2014 19:56:33 GMT

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

A Poor/Rich SAS Users Proc Export - sasCommunity )

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:

%macro copy2excel(lib=,mem=,outpath=,outwb=);

proc fcmp outlib=work.func.util;

function c2cb(lib $,mem $);

rc=filename('clippy',' ','clipbrd');

if rc ne 0 then return(1);

fid=fopen('clippy','o',0,'v');

if fid eq 0 then do;

rc = filename( 'clippy' );

return(2);

end;

dsid=open(catx('.',lib,mem));

if dsid eq 0 then do;

rc=fclose(fid);

rc=filename('clippy');

return(3);

end;

rc=attrn(dsid,'any');

if rc ne 1 then do;

rc=fclose(fid);

rc=close(dsid);

rc=filename('clippy');

return(4);

end;

nvar=attrn(dsid,'nvar');

array v[1] /nosymbols;

call dynamic_array(v,nvar);

do i = 1 to nvar;

/* if i gt 1 then rc=fput(fid,'09'x);*/

/* rc=fput(fid,varname(dsid,i));*/

v=ifn( vartype(dsid,i)='C',1,2);

end;

/* rc=fwrite(fid);*/

do i=1 to attrn(dsid,'nlobs');

rc=fetchobs(dsid,i);

do j=1 to nvar;

if j gt 1 then rc=fput(fid,'09'x);

if (v eq 1) then rc=fput(fid, getvarc(dsid,j));

else do;

fmt=varfmt(dsid,j) ;

if missing(fmt) then fmt='best12.';

rc=fput(fid,putc(putn(getvarn(dsid,j ),fmt ),'$char12.'));

end;

rc=fwrite(fid);

end;

rc=fclose(fid);

rc=close(dsid);

rc=filename('clippy');

return(0);

endsub;

quit;

%local cmplib;

%let cmplib=%sysfunc(getoption(cmplib));

options cmplib=(work.func);

%put %sysfunc(c2cb(&lib,&mem)) ;

options cmplib=(&cmplib);

data _null_;

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 'Newbook.Sheets("Sheet1").Activate';

put 'Newbook.Worksheets("Sheet1").Paste';

put 'Newbook.SaveAs("'@;

put "&outpath.&outwb."@;

put '")';

put "Newbook.Close";

put 'objExcel = Nothing';

put 'Newbook = Nothing';

script="'"||'cscript "'||trim(script)||'"'||"'";

call symput('script',script);

run;

data _null_;

call system(&script.);

run;

%mend copy2excel;

options NOQUOTELENMAX;

%copy2excel(lib=sashelp,

mem=class,

outpath=d:\art\,

outwb=exceldelete.xlsx)