BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
22 REPLIES 22
deleted_user
Not applicable
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
deleted_user
Not applicable
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
deleted_user
Not applicable
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
deleted_user
Not applicable
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
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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.
deleted_user
Not applicable
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
deleted_user
Not applicable
Thanks Peter

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

PUT (_ALL_)(:);
Duong
Obsidian | Level 7
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
deleted_user
Not applicable
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
Michael_W
Calcite | Level 5
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?
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Michael_W
Calcite | Level 5
Thanks Scott. I am pretty new to this forum, so I am still getting used to the rules of the road, so to speak. I will re-post as a new topic.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 22 replies
  • 6331 views
  • 0 likes
  • 5 in conversation