topic Re: How to Remove Start and End Blank Lines from Custom ODS CSV Tagset in ODS and Base Reporting

How to Remove Start and End Blank Lines from Custom ODS CSV Tagset

SASKiwi — Thu, 01 Sep 2011 03:20:22 GMT

Greetings ODS Tagset Gurus.

I am working on a customised CSV tagset that has the following changes:

Does not put out the usual header row with column names.
Does not put quotes around text values.

This I have done successfully. The problem remains is that the CSV file created contains a blank line at both the top and the bottom.

I started with the custom tagset from here:

http://support.sas.com/documentation/cdl/en/odsug/62755/HTML/default/viewer.htm#n1ln06jm8i5ttnn1qg5mz4xj4koi.htm

and tweaked it like this:

proc template;

define

notes 'This is the MDR CSV template';

define event table;

start:

put 'Start';

finish:

put 'End';

end;

define event put_value;

put VALUE;

end

;

define

event put_value_cr;

put

VALUE NL;

end

;

define

event row;

finish:

put

NL ;

end

;

define event header;

start:

put ',' /if ^cmp( COLSTART, '1');

put '''';

put VALUE;

finish:

put '''';

end;

define

event data;

start:

put ',' /if ^cmp( COLSTART, '1'

);

/* put ''''; */

put

VALUE;

/* finish:

put ''''; */

end

;

define

event colspanfill;

put ','

;

end

;

define

event rowspanfill;

put ','

/if ^exists( VALUE);

end

;

define

event breakline;

put

NL;

end

;

define

event splitline;

put

NL;

end

;

registered_tm =

'(r)'

;

trademark =

'(tm)'

;

'(c)'

;

output_type =

'csv'

;

stacked_columns = OFF;

end

;

run

;

I then tested the tagset like this:

ods Tagsets.csv_mdr file = "test.csv" ;

proc print data = sashelp.class noobs;

run;

ods Tagsets.csv_mdr close;

ods listing;

I am running SAS 9.1.3 SP4 under Windows. I suspect a tweak of the row event is required because if you comment this out all data ends up on the same line. Would appreciate some help in what this tweak should be!

tagset Tagsets.csv_mdr;

Re: How to Remove Start and End Blank Lines from Custom ODS CSV Tagset

Cynthia_sas — Thu, 01 Sep 2011 14:03:53 GMT

Hi:

I would use a different approach. I would use PROC REPORT with the NOHEADER option to get rid of the column headers in the report file. Then I would just write a tagset template that inherited everything from regular CSV, but only got rid of the quotes. There is an EVENT for QUOTES. I would essentially turn it off, as shown in the code below. This bypasses the need to tinker with the ROW event. If you want to pursue your solution, then I suggest you try to track down the "extra" lines by finding the places where you write NL (newline) by selectively taking them out until you find the one(s) that are causing the issue.

cynthia

proc template;
   define tagset Tagsets.myCsv;
   parent=tagsets.csv;
      notes "This is the changed CSV definition no quotes";
      define event quotes;
         start:
           break;

         finish:
          break ;
      end;

   end;
run;

ods tagsets.mycsv file='c:\temp\test2.csv';

proc report data=sashelp.shoes noheader nowd;
where region = 'Western Europe';
column region subsidiary product sales inventory;
run;

ods tagsets.mycsv close;

Re: How to Remove Start and End Blank Lines from Custom ODS CSV Tagset

SASKiwi — Tue, 06 Sep 2011 04:44:24 GMT

Great solution Cynthia. However I want to scale this up to a dataset with 100,000 rows and 64 columns. Unfortunately PROC REPORT fails with out of memory errors on this volume of data with the CSV tagset. PROC PRINT works fine. I have managed to get it working with PROC PRINT using the official CSV tagset (change in bold) but only when the last column is fully populated, by using an event row like below. But I really want it to handle even totally missing columns. Thanks a lot for your help.

define event row;

finish:

do /if $cell_count;

close;

eval $cell_count 0;

/*-----------------------------------------------------------eric-*/

/*-- End of second row, only thing there is the --*/

/*-- rowheaders. print the data cell that were saved --*/

/*-- from the row above. --*/

/*--------------------------------------------------------17Nov03-*/

else /if $$row;

put $$row;

unset $$row;

done;

put nl / if $VALUE ;

end;

Re: How to Remove Start and End Blank Lines from Custom ODS CSV Tagset

SASKiwi — Fri, 16 Sep 2011 05:03:03 GMT

In the end I solved this without ODS. OK, its not as flexible, but at least it gets the job done, and is pretty quick!

%macro export_csv_noheader (dsname =

,outfile =

);

filename exTemp temp;

proc export data = &dsname

outfile = exTemp

dbms = csv

REPLACE

;

quit;

data _null_;

infile extemp firstobs = 2;

file &outfile;

input;

put _infile_;

run;

filename exTemp CLEAR;

%mend export_csv_noheader;

Re: How to Remove Start and End Blank Lines from Custom ODS CSV Tagset

Ksharp — Thu, 22 Sep 2011 07:17:11 GMT

There is no more another datastep. use putnames=no option.

proc export data = sashelp.class

outfile = 'c:\class.csv'

dbms = csv

REPLACE

;

putnames=no;

quit;

Ksharp

Re: How to Remove Start and End Blank Lines from Custom ODS CSV Tagset

SASKiwi — Thu, 22 Sep 2011 20:29:22 GMT

Thanks Ksharp. Unfortunately I forgot to mention I am limited to SAS 9.1.3, and PUTNAMES only came in with 9.2. I live in hope that we may get upgraded some day....