topic Re: Supressing Column Header using ODS in ODS and Base Reporting

Supressing Column Header using ODS

deleted_user — Tue, 20 Nov 2007 06:30:10 GMT

Hi All

I am trying to output a csv file but I do not want to see the Column Headers. I can do this by using the LABEL VARIABLE='00'x statement, but then when I open the csv file in excel, I see a blank line at the top on row 1, how can I remove this row as well in SAS before outputing the file, so that the output only consists of the data. I need to achieve this because this data has to be fed into another program.

Part of the program:

DATA OUTCSV2;
SET DEALER_DETAILS2;
FILENAME CSVOUT2 'B23.D075.ODS(CSV2)'
DSNTYPE=LIBRARY DSORG=PO
DISP=(SHR,CATLG,DELETE);
ODS CSV FILE=CSVOUT2 STYLE=NEWSTYLE2 TRANTAB=ASCII;
PROC PRINT DATA=DEALER_DETAILS2 NOOBS LABEL;
VAR DIAL DLRNUM SAPACC TWN PDEALERN PNAME PHONE_NUM;
LABEL DIAL='00'x DLRNUM='00'x SAPACC='00'x TWN='00'x PDEALERN='00'x
PNAME='00'x PHONE_NUM='00'x;
TITLE 'DEALERSHIP DETAILS';
RUN;
ODS CSV CLOSE;
ODS LISTING;

Thanks alot for your help.
Regards
Shelton.

Re: Supressing Column Header using ODS

deleted_user — Tue, 20 Nov 2007 08:43:40 GMT

don't use ODS, if it's not doing what you want.
just use a simple data step.
It's shorter![pre]
data _null_;
file 'your.file.csv' dsd lrecl=10000 ;
set your.data ;
put (_all_)(:);
run;[/pre]

PeterC

Re: Supressing Column Header using ODS

deleted_user — Tue, 20 Nov 2007 23:39:13 GMT

Hi Peter

Thanks for the response. Will this code work on the mainframe - we are using OS/390.

Also I need to FTP this file out to a Windows box.

Thanks! Message was edited by: sdcruz

Re: Supressing Column Header using ODS

Cynthia_sas — Wed, 21 Nov 2007 01:14:37 GMT

Hi:
Here's what I don't understand...you seem to be mixing a DATA step with an ODS CSV step...you don't need both...since your DATA step is writing to a WORK library, I'm figuring you don't use WORK.OUTCSV2 at all. Here's what you have (***** are my divider lines/comments on your posted code):
[pre]
***** creating a WORK data set here??? why??? ;
DATA OUTCSV2;
SET DEALER_DETAILS2;

***** FILENAME is a global statement. It takes effect as soon as it is encountered ;
***** and stays in effect until you end your session or change or clear the ;
***** filename statement. For this reason, you usually find GLOBAL ;
***** statements OUTSIDE a DATA step program boundary.;
FILENAME CSVOUT2 'B23.D075.ODS(CSV2)'
DSNTYPE=LIBRARY DSORG=PO
DISP=(SHR,CATLG,DELETE);

**** OK, here you start the creation of CSV output and write it to your PDS;
ODS CSV FILE=CSVOUT2 STYLE=NEWSTYLE2 TRANTAB=ASCII;

****** PROC PRINT will be step boundary for DATA OUTCSV2 step ;
PROC PRINT DATA=DEALER_DETAILS2 NOOBS LABEL;
VAR DIAL DLRNUM SAPACC TWN PDEALERN PNAME PHONE_NUM;
LABEL DIAL='00'x DLRNUM='00'x SAPACC='00'x TWN='00'x PDEALERN='00'x
PNAME='00'x PHONE_NUM='00'x;
TITLE 'DEALERSHIP DETAILS';
RUN;
ODS CSV CLOSE;
ODS LISTING; [/pre]

Here's how you could simplify it and not have headers -- using '00'x is a good trick, but PROC REPORT has an option that lets you suppress headers completely:
[pre]

** only need FILENAME statement. Do not need DATA step;
** use FILENAME to point to your PDS member;
FILENAME CSVOUT2 'B23.D075.ODS(CSV2)'
DSNTYPE=LIBRARY DSORG=PO
DISP=(SHR,CATLG,DELETE);

** style not used for ODS CSV;
** add rs=none just in case you find the CSV lines wrapping funny after;
** you FTP -- RS=NONE tells ODS to write out 1 line of output at a time;
ODS CSV FILE=CSVOUT2 TRANTAB=ASCII rs=none;

PROC REPORT DATA=DEALER_DETAILS2 NOWD NOHEADER;
COLUMN DIAL DLRNUM SAPACC TWN PDEALERN PNAME PHONE_NUM;
TITLE 'DEALERSHIP DETAILS';
RUN;

ODS CSV CLOSE;
[/pre]

If you REALLY want a DATA SET from the job, then do this:
[pre]
** this is a made up name for a SAS library;
libname PERMLIB 'B23.D075.PERMSAS' DISP=OLD;

DATA PERMLIB.OUTCSV2;
SET DEALER_DETAILS2;
RUN:
[/pre]

If you want to see the titles in the CSV file, then do this:
[pre]
ODS CSVALL FILE=CSVOUT2 TRANTAB=ASCII rs=none;
(your code)
ODS CSVALL CLOSE;
[/pre]

Or, you could write to the file directly with a data step. Or you could use the
%DS2CSV macro or you could use the %FLATFILE macro:
http://ftp.sas.com/techsup/download/misc/flatfile.pdf
http://support.sas.com/kb/26/085.html
http://www2.sas.com/proceedings/sugi27/p174-27.pdf

cynthia

Re: Supressing Column Header using ODS

deleted_user — Wed, 21 Nov 2007 15:48:51 GMT

getting the MF to write to somewhere else, needs an FTP server on that somewhere else, and an account on that server which your MF program is allowed to use. You can write directly to the ftp server from the data step, which eliminates all the file/space allocation issues on the mainframe.
Take my earlier code and preceed it with a filename statement using the FTP engine ( for details, see online-doc at http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000178980.htm.) a bit like
[pre]filename csvout ftp .......................................... ;
data _null_;
file csvout dsd lrecl=10000 ;
set your.data ;
put (_all_)(:);
run;
filename csvout clear ;[/pre]
the ftp parameters are subject to your ftp server and the local administrators rules

Good Luck

PeterC

Re: Supressing Column Header using ODS

deleted_user — Fri, 30 Nov 2007 01:29:09 GMT

Hi Cynthia

I tried your code but cannot open the file in Excel. I get a window pop up just after I double click on the file to open it saying "File Not Completely Loaded"

Hitting the Help button I get the following message:
This message can appear if:
You are trying to open a file that contains more than 65,536 rows or 256 columns. To fix this problem, open the source file in a text editor such as Microsoft Word. Save the source file as several smaller files that conform to this row and column limit, and then open the smaller files in Excel. If the source data cannot be opened in a text editor, try importing the data into Microsoft Access, and then exporting subsets of the data from Access to Excel.
You are trying to paste tab-delimited data into an area that is too small. To fix this problem, select an area in the worksheet large enough to accommodate every delimited item.
Notes
You can not configure Excel to exceed the limit of 65,536 rows and 256 columns.
By default, Excel places three worksheets in a workbook file. Each worksheet can contain 65,536 rows and 256 columns of data, and workbooks can contain more than three worksheets if your computer has enough memory to support the additional data.

I haven't tried anyother of the options you mentioned yet.

Re: Supressing Column Header using ODS

deleted_user — Fri, 30 Nov 2007 01:54:11 GMT

Hi Peter

Will your code remove the blank line from the top of the csv file where the "" are present ?

I will wait for Cynthia's reponse.

Regards

Re: Supressing Column Header using ODS

Cynthia_sas — Fri, 30 Nov 2007 04:00:46 GMT

Hi:
Well, the CSV destination is NOT creating tab-delimited files. So, is there a possibility that your data file is more than or close to 65,536 rows???

A CSV file should open in Excel 97 and higher. Once you FTP'd it from the MF to Windows, did you give the file an extension of .CSV???

If your file is NOT that big, you could try this code that uses SASHELP.SHOES and see if it has the same issue.
[pre]
data newshoes;
set sashelp.shoes;
do i = 1 to 160;
x+1;
output;
end;
run;

ods csv file='testfile.csv' trantab=ascii rs=none;

proc report data=newshoes nowd noheader;
column x region subsidiary product sales;
run;

ods csv close;
[/pre]
On my system, this code creates a file with no headers and with 63,200 rows. The file opens in Excel 2002/2003 with no problems. However, if I go above 65,536 rows, then I get the same error message. If your Excel does not open the file of 63,200 rows and you get the same error message, then I'd suspect a problem with Excel. If your Excel does open the file created above, but still does not open your original file, then your only choice may be to break up the file or open it in a Notepad/text editor to see what might be wrong with it.

Or you might consider contacting Tech Support for more help with this issue.

cynthia

Re: Supressing Column Header using ODS

deleted_user — Fri, 30 Nov 2007 05:42:50 GMT

The file size may indeed be the problem, or incorrect file naming as Cynthia has suggested. If these cannot be proven, just make sure that that file was correctly sent from the mainframe.

Did you use ASCII or Binary transfer to send the file? The difference is possible misinterpretation of certain file bytes because of different character sets.

However this turns out, if it isn't working then Tech Support is needed so they can replicate the environment and step you through a series of tests to identify the cause of the problem.

Kind regards

David

Re: Supressing Column Header using ODS

deleted_user — Fri, 30 Nov 2007 06:12:47 GMT

When I open the csv file (yes it is named .csv, has 200 records and sent as Binary in the FTP step) it has one line and goes right across the spreadsheet horizontally on one row up to column IV (the maximum column i guess in Excel)

This is my new code - I need to create 2 CSV files - one with the column headings and another with a reduced number of columns and no headings.

/* ODS FOR CSV OUTPUT
DATA OUTCSV;
SET DEALER_DETAILS;
FILENAME CSVOUT 'B23.D075.ODS(CSV)'
DSNTYPE=LIBRARY DSORG=PO
DISP=(SHR,CATLG,DELETE);

ODS CSV FILE=CSVOUT STYLE=NEWSTYLE TRANTAB=ASCII;
PROC PRINT DATA=DEALER_DETAILS NOOBS;
TITLE 'DEALERSHIP DETAILS';
VAR DIAL DLRNUM SAPACC TWN PDEALERN PSTREET STATE PCD CNTY
E_MAIL PHONE_NUM FAX_NUM PNAME TM TCSM SSCTM_ SSCAM
AG CCE PS;

RUN;
ODS CSV CLOSE;
ODS LISTING;

FILENAME CSVOUT2 'B23.D075.ODS(CSV2)'
DSNTYPE=LIBRARY DSORG=PO
DISP=(SHR,CATLG,DELETE);
ODS CSV FILE='CSVOUT2.csv' TRANTAB=ASCII rs=none;

PROC REPORT DATA=OUTCSV NOWD NOHEADER;
COLUMN DAIL DLRNUM SAPACC TWN PDEALERN PNAME PHONE_NUM;
RUN;
ODS CSV CLOSE;

Message was edited by: sdcruz Message was edited by: sdcruz

Re: Supressing Column Header using ODS

deleted_user — Fri, 30 Nov 2007 15:07:33 GMT

Shelton

you asked if my code would remove the blank line from the top.....

Try this version modified to run in display manager on a mainframe and write a file locally.
It will probably ask you if you want to create the file named .your.file.csv. Allow that .
[pre]data _null_;
file '.your.file.csv' dsd lrecl=10000 ;
set sashelp.class ; *** or your.data if small enough for test ;
put (_all_)(:);
run;
proc FSlist file= '.your.file.csv' ; run;
[/pre]
PROC FSlist will show you the file after creating it. There should be no header, not even an empty one.

Good Luck

PeterC

Re: Supressing Column Header using ODS

Cynthia_sas — Fri, 30 Nov 2007 17:20:03 GMT

Hi:
I am still having a hard time with your code. Here are my questions:
1) What is the purpose of this code -- why not just use DEALER_DETAILS in the PROC REPORT step???? What is OUTCSV for anyway???? Why isn't there a RUN statement after the DATA step?:
[pre]
DATA OUTCSV;
SET DEALER_DETAILS;
.
.
.
ODS CSV FILE='CSVOUT2.csv' TRANTAB=ASCII rs=none;

PROC REPORT DATA=OUTCSV NOWD NOHEADER;
COLUMN DAIL DLRNUM SAPACC TWN PDEALERN PNAME PHONE_NUM;
RUN;
ODS CSV CLOSE;

[/pre]

When you say that you need to create 2 CSV files -- is that by choice? PS...is the variable name DAIL or DIAL in the COLUMN statement??

Also, it may not make any difference, but you might try NOT writing your CSV file to a partitioned data set -- in the case where you are creating a "flat" file -- where the possible record length of the flat file "line" may or may not "fit" in the PDS record length -- it's possible that the lines might be broken oddly when you FTP -- although that's unlikely these days with PDSE.

I'm not sure whether you have what you want or not??? Possibly you've gotten too much wrong advice here on the forum because we've all misunderstood what you want the outcome to be. Tech Support can look over what you have and what you want and tell you the best method to accomplish that goal.

cynthia

Re: Supressing Column Header using ODS

deleted_user — Mon, 03 Dec 2007 05:31:45 GMT

Hi Peter

Thanks for your comments - i tried your code and I can now supress the headers. The amazing thing is that I didnt have to use ODS - simply ftp the contents in a normal dataset file using ASCII mode. Code looks like this:

DATA FINALOUT;
FILE 'B23.D075.REPORT.DLRCSV' DSD LRECL=10000 ;
SET BRINGIN;
PUT (_ALL_)(:);
RUN;

Thanks again Peter and Cynthia for taking the time to reply.

Regards,
Shelton.

Re: Supressing Column Header using ODS

deleted_user — Mon, 03 Dec 2007 09:01:44 GMT

Shelton

the magic of ODS is most valuable when you want "output" that will require some "surface". Here you wanted no surface - no header - just data. "Data" has been the strength of the SAS System for over 30 years.
The ODS CSV destination makes light work of controlling it's "surface", by which I mean the descriptive column headers and the column order.
Where you want or need more formatting, for example : bookmarks in PDF; or T.O.C. layout; or formatting within excel; there is no satisfactory substitute for ODS.
Your requirement for data without headers sounded like a call for the elemental features of "naked" base SAS.
When you need to deliver large quantities of data from SAS data sets to csv files on another platform, you may find the FTP engine of the filename statement helpful. It means that through FTP you can write directly into the destination from the datastep. That removes two passes through the data, as well as removing the need to find space on the sending platform for the csv-format data file. (It also saved a lot of time when I was regularly shifting 10gb of month end datamarts in csv format from os390 to aix, during the parallel running of the transition of a data warehouse to the aix platform)

However I recommend that you polish up your confidence in reporting with ODS. It's far more rewarding than base SAS and data _null_ reporting without ODS.

Good Luck

PeterC

Re: Supressing Column Header using ODS

deleted_user — Mon, 03 Dec 2007 22:14:29 GMT

Thanks Peter

BTW what does (:) do in the following statement ?

PUT (_ALL_)(:);

Re: Supressing Column Header using ODS

Duong — Mon, 03 Dec 2007 23:24:31 GMT

Hi Sdcruz

I think I understand what you are trying to achieve.

the ODS cvs tagset has a bug that is it will give you a blank first row. SI has corrected this but has given it a new tagset name (cvsall). So you can use this cvsall instead.

ods tagset csvall file= ...etc;
proc print data=mydata;
var a b etc;
run;
ods close _all_;

I think this still give you the first row as the variables name though. Or I think there is
an option for no header label. If not you can get rid of this by modifying the csvall tagset. I don't this is difficult - If you look at the csvall tagset it is pretty small.

I preferred this method now over the data step method with the put statement.

Hope this is what you are after.

Duong

Re: Supressing Column Header using ODS

deleted_user — Tue, 04 Dec 2007 08:58:54 GMT

That colon(:) is a format modiifier, in this case, for formats on a PUT statement.
The style of PUT statement is documented as PUT Statement, Formatted, in the on-line doc at
http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000175758.htm.
There, the second variation in Syntax shows the technique with parentheses.
The
(_all_)
is the list of variables to be PUT, and
(:)
is the list of formats to apply.
Effectively the colon adds no more formatting, but something is needed at that point.
The colon format modifier is documented at
http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000176623.htm.
There, example 3 shows a simpler demonstration of the colon format modifier.

PeterC

Re: Supressing Column Header using ODS

Michael_W — Wed, 09 Sep 2009 00:20:09 GMT

Greetings Peter,

I have a similar dilemna in that I am trying to post-process an RTF file to remore a couple of blank lines. I read in the RTF file using an INFILE statement and delete the two roms containing the RTF control words that are causing it to include the extra two blank lines. I tried using the code you provided earlier in this thread, however when I attempt to open the outputted RTF with Word, it says it cannot open the specified file (as if it were corrupt). When I open the original file with NotePad and then manually remove the same to lines, save it and then re-open with Word ...it works fine. Any ideas on how I can achieve my objective?

Re: Supressing Column Header using ODS

sbb — Wed, 09 Sep 2009 01:37:10 GMT

Suggestion: It's not a good idea to piggy-back on someone else's post, especially one from years ago. If you must, then paste the link to the referenced post, and begin your own separate, independently authored post.

Scott Barry
SBBWorks, Inc.

Re: Supressing Column Header using ODS

sbb — Wed, 09 Sep 2009 01:40:14 GMT

What happens after you completely terminate your SAS session - are you then able to open the file with Word? While Word is open, even if the particular document is not open, you will see a backup-copy in the same-named folder where the primary document resides, with a leading "$" I believe. This means that Word still has control over the document, until you close Word altogether.

Scott Barry
SBBWorks, Inc.