topic Re: export data set to Excel but NO headers pleazze in SAS Programming

export data set to Excel but NO headers pleazze

Jaheuk — Wed, 24 Nov 2010 16:11:44 GMT

hi all,
proc EXPORT, LIBNAME engine to Excel ALL fine but I do not want headers in my sheet!!
Any ideas ??

Regards,
Herman

Re: export data set to Excel but NO headers pleazze

Cynthia_sas — Wed, 24 Nov 2010 16:15:23 GMT

Hi:
Are you willing to use ODS??? PROC REPORT has a NOHEADER option that will get rid of your column headers. This is a quick way to achieve what you want.

cynthia
[pre]
ods csv file='c:\temp\nohead.csv';

proc report data=sashelp.class nowd noheader;
column name sex age height weight;
run;

ods csv close;
[/pre]

Re: export data set to Excel but NO headers pleazze

Peter_C — Wed, 24 Nov 2010 20:16:59 GMT

or Herman,
are you willing to consider creating just the data, in a CSV file
Assuming you want ALL columns and rows, and in their default order and formats, it is very brief
data _null_;
File 'your.csv' dsd lrecl=10000;
Set your.dataset ;
Put ( _all_)(:);
run;
That's all!
then let excel open 'your.csv'

peterC

Re: export data set to Excel but NO headers pleazze

Ksharp — Thu, 25 Nov 2010 01:38:22 GMT

Hi.
When I look up documentation about excel,I found another proc can do it.

[pre]
proc dbload dbms=excel data=sashelp.class;
path='c:\mydata.xls';
putnames=no;
limit=0;
load;
run;
[/pre]

Ksharp Message was edited by: Ksharp

Re: export data set to Excel but NO headers pleazze

Cynthia_sas — Thu, 25 Nov 2010 04:01:30 GMT

Hi:
It is also worth noting (from the doc) that DBLOAD will only create Excel 5/95 files. It says here: http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003080545.htm
that: The DBLOAD procedure does not support Excel 97 or later files.

You will have to evaluate whether you want to create an older version of an Excel file format.

Also, there are some Tech Support notes about using DBLOAD, including this one;
http://support.sas.com/kb/13/954.html indicates that with SAS 9.1.3, there was an issue with DBLOAD applying numeric formats correctly.

cynthia

Re: export data set to Excel but NO headers pleazze

Ksharp — Fri, 26 Nov 2010 04:46:37 GMT

[pre]
PROC EXPORT DATA=sashelp.shoes FILE="c:\new.xls" DBMS=XLS REPLACE;
SHEET='Invoice';
putnames=no;
RUN;
[/pre]

Re: export data set to Excel but NO headers pleazze

Jaheuk — Fri, 26 Nov 2010 09:37:22 GMT

I hoped this was the solution.....however the software who needs this excel can't deal with dbms=XLS!
It should be at least EXCEL (I tested it) .... and putnams is only available with XLS
Look at
http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003103761.htm

thanks all for responding!!!

Regards,
Herman

Re: export data set to Excel but NO headers pleazze

sbb — Fri, 26 Nov 2010 14:55:10 GMT

To the OP: you never replied to Cynthia where she suggested using ODS CSV. Also, it is unclear what is meant by needing "...the software who needs....it should be at least EXCEL" -- are you getting some type of specific error when attempting to read the SAS-generated DBMS=XLS file? Please provide a clear problem symptom. Also it is useful to include SAS version, the operating system information, to start with.

Scott Barry
SBBWorks, Inc.

Re: export data set to Excel but NO headers pleazze

Jaheuk — Fri, 26 Nov 2010 16:50:17 GMT

TO MISTER SCOTT BARRY: may I kindly ask you stop replying to my questions!!
I am really getting tired of you stupid remarks!! Get a life...go fishing!!

H.

Re: export data set to Excel but NO headers pleazze

ArtC — Fri, 26 Nov 2010 20:08:18 GMT

This is a professional forum. You have asked for free help from folks who are taking time from their lives to give you free assistance. Please keep the conversation civil. If you do not care for a reply then ignore it, but please consider that your response will be read by a much larger audience.

Re: export data set to Excel but NO headers pleazze

Ksharp — Wed, 23 Feb 2011 07:01:22 GMT

It looks like that The only way is similar with Cynthia mentioned.

[pre]
ods tagsets.excelxp file='c:\temp\nohead.xls';

proc report data=sashelp.class nowd noheader;
column name sex age height weight;
run;

ods tagsets.excelxp close;

[/pre]

Ksharp