Supressing Column Header using ODS

[Michael_W]

Exiting EG didn't resolve the problem. The actual error message that Word is generating is "Word cannot read the formatting in this document".

[sbb]

Please open a new post to the forum/group and provide (COPY / PASTE) the SAS log with your expanded code and SAS diagnostics messages and NOTES.

Scott Barry
SBBWorks, Inc.

[sbb]

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.

[Cynthia_sas]

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

[deleted_user]

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.

[Cynthia_sas]

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

[deleted_user]

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

[deleted_user]

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