DATA Step, Macro, Functions and more

export data set to Excel but NO headers pleazze

Reply
Frequent Contributor
Posts: 89

export data set to Excel but NO headers pleazze

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

Regards,
Herman
SAS Super FREQ
Posts: 8,742

Re: export data set to Excel but NO headers pleazze

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]
Valued Guide
Posts: 2,174

Re: export data set to Excel but NO headers pleazze

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_)(Smiley Happy;
run;
That's all!
then let excel open 'your.csv'


peterC
Super User
Posts: 9,671

Re: export data set to Excel but NO headers pleazze

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
SAS Super FREQ
Posts: 8,742

Re: export data set to Excel but NO headers pleazze

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
Super User
Posts: 9,671

Re: export data set to Excel but NO headers pleazze

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

Re: export data set to Excel but NO headers pleazze

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
Super Contributor
Super Contributor
Posts: 3,174

Re: export data set to Excel but NO headers pleazze

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.
Frequent Contributor
Posts: 89

Re: export data set to Excel but NO headers pleazze

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.
Valued Guide
Posts: 632

Re: export data set to Excel but NO headers pleazze

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.
Super User
Posts: 9,671

Re: export data set to Excel but NO headers pleazze

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
Ask a Question
Discussion stats
  • 10 replies
  • 4523 views
  • 0 likes
  • 6 in conversation