topic Export to csv without extra commas in SAS Programming

Export to csv without extra commas

notepadd123 — Tue, 06 Apr 2021 22:33:23 GMT

Hi,

Row 1 of my dataset is variable name, row 2 of my dataset is my dataset header, starting from row 3 and onwards are the rest of my dataset records. Like this:

var1	var2	var3	var4	var5	var6	var7
header cell 1	header cell 2	header cell 3	header cell 4
1.01	2.01	3.01	4.01	5.01	6.01	7.01
2.01	3.01	4.01	5.01	6.01	7.01	8.01
3.01	4.01	5.01	6.01	7.01	8.01	9.01
4.01	5.01	6.01	7.01	8.01	9.01	10.01

When export dataset, I would like to only obtain dataset header and the main dataset records. I tried two method to export:

Method 1

proc export data=work.datasetout dbms=csv
outfile="fileout.csv"
replace;
putnames=no;
run;

Method 2

ods csv file= "fileout.csv" options(sheet_interval='NONE');
proc report data=work.table_02_Header nowd noheader;
run;
proc report data=work.rawtable02_03 nowd noheader;
run;
ods csv close;

However my dataset header occupied less columns than the dataset records and result in extra commas in the header if open output csv with notepad using Method 1 :

header cell 1,header cell 2,header cell 3,header cell 4,,,
1.01,1.02,1.03,1.04,1.05,1.06,1.07
2.01,2.02,2.03,2.04,2.05,2.06,2.07
3.01,3.02,3.03,3.04,3.05,3.06,3.07
4.01,4.02,4.03,4.04,4.05,4.06,4.07

And create extra line between header and dataset if Method 2 is used:

header cell 1,header cell 2,header cell 3,header cell 4

->
1.01,1.02,1.03,1.04,1.05,1.06,1.07
2.01,2.02,2.03,2.04,2.05,2.06,2.07
3.01,3.02,3.03,3.04,3.05,3.06,3.07
4.01,4.02,4.03,4.04,4.05,4.06,4.07

Notice in the header row we have 4 records only, but the main dataset has 7 columns.

My question is how to exclude the extra commas in when exporting to csv file in Method 1 or exclude the blank line in Method 2 in sas?

Any suggestions would help! Thank you!!

Re: Export to csv without extra commas

ballardw — Tue, 06 Apr 2021 23:25:54 GMT

@notepadd123 wrote:

Hi,

Row 1 of my dataset is variable name, row 2 of my dataset is my dataset header, starting from row 3 and onwards are the rest of my dataset records. Like this:

var1 var2 var3 var4 var5 var6 var7

header cell 1 header cell 2 header cell 3 header cell 4

1.01 2.01 3.01 4.01 5.01 6.01 7.01

2.01 3.01 4.01 5.01 6.01 7.01 8.01

3.01 4.01 5.01 6.01 7.01 8.01 9.01

4.01 5.01 6.01 7.01 8.01 9.01 10.01

When export dataset, I would like to only obtain dataset header and the main dataset records. I tried two method to export:

Method 1

proc export data=work.datasetout dbms=csv
outfile="fileout.csv"
replace;
putnames=no;
run;

Method 2

ods csv file= "fileout.csv" options(sheet_interval='NONE');
proc report data=work.table_02_Header nowd noheader;
run;
proc report data=work.rawtable02_03 nowd noheader;
run;
ods csv close;

However my dataset header occupied less columns than the dataset records and result in extra commas in the header if open output csv with notepad using Method 1 :

header cell 1,header cell 2,header cell 3,header cell 4,,,
1.01,1.02,1.03,1.04,1.05,1.06,1.07
2.01,2.02,2.03,2.04,2.05,2.06,2.07
3.01,3.02,3.03,3.04,3.05,3.06,3.07
4.01,4.02,4.03,4.04,4.05,4.06,4.07

And create extra line between header and dataset if Method 2 is used:

header cell 1,header cell 2,header cell 3,header cell 4

->
1.01,1.02,1.03,1.04,1.05,1.06,1.07
2.01,2.02,2.03,2.04,2.05,2.06,2.07
3.01,3.02,3.03,3.04,3.05,3.06,3.07
4.01,4.02,4.03,4.04,4.05,4.06,4.07

Notice in the header row we have 4 records only, but the main dataset has 7 columns.

My question is how to exclude the extra commas in when exporting to csv file in Method 1 or exclude the blank line in Method 2 in sas?

Any suggestions would help! Thank you!!

If you use "putnames=no" then you should not have any "header" row at all. The extra commas would be because you have missing values for some variables in the first record.

Example data set and export:

data example;
   input x1-x7;
datalines;
1 2 3 4 5 . .
11 22 33 44 55 66 77
;

proc export data=example dbms=csv
outfile="D:\Users\Owner\Documents\junk\file.csv"
replace;
putnames=no;
run;

Which creates an output of :

1,2,3,4,5,,
11,22,33,44,55,66,77

If I use:

proc export data=example dbms=csv
outfile="D:\Users\Owner\Documents\junk\file2.csv"
replace;
putnames=no;
run;

Then the result is:

x1,x2,x3,x4,x5,x6,x7
1,2,3,4,5,,
11,22,33,44,55,66,77

where you can see the extra commas are from missing data.

So if your first code is creating any sort of "headers" then you need to show us what the actual content of that data file is using a data step such as I have shown above.

I can't quite follow which code is doing what but Proc Report behaves differently because it is a report procedure and creates "stuff" that helps readability of a report and may not translate well to a text format like csv.

Re: Export to csv without extra commas

Sajid01 — Wed, 07 Apr 2021 00:37:13 GMT

The result you are getting are perfectly OK.

The issue is with your dataset. Perhaps it was imported from an external file using SAS EG or something like that.

The way the dataset is given in your question, it appears that the var1 ...var7 are the column names that have been given by SAS EG etc.

The first row of the dataset (header_cell_1 etc) is populated only in four columns, the other three (var5 -var7) are empty.
That is why you are getting extra commas.
The solution lies in correcting at the source.
To get a better understanding try your method 1 code using sashelp.class as the dataset.

proc export data=sashelp.class dbms=csv
outfile="fileout.csv"
replace;
putnames=no;
run;

You will get a perfect answer.
Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
Carol,F,14,62.8,102.5
Henry,M,14,63.5,102.5
James,M,12,57.3,83
Jane,F,12,59.8,84.5
Janet,F,15,62.5,112.5
Jeffrey,M,13,62.5,84
John,M,12,59,99.5
Joyce,F,11,51.3,50.5
Judy,F,14,64.3,90
Louise,F,12,56.3,77
Mary,F,15,66.5,112
Philip,M,16,72,150
Robert,M,12,64.8,128
Ronald,M,15,67,133
Thomas,M,11,57.5,85
William,M,15,66.5,112

Re: Export to csv without extra commas

ChrisNZ — Wed, 07 Apr 2021 00:41:03 GMT

The missing columns are for columns VAR5 to VAR7.

Drop them.

proc export data=work.datasetout(keep=VAR1--VAR4)

Re: Export to csv without extra commas

Tom — Wed, 07 Apr 2021 05:01:03 GMT

So you want to write 4 fields on the first row and 7 fields on the rest of the rows?

data _null_;
  set datasetout ;
  file "fileout.csv" dsd;
  if _n_=1 then put var1-var4;
  else put var1-var7;
run;

Re: Export to csv without extra commas

notepadd123 — Wed, 07 Apr 2021 16:37:55 GMT

Thank you Tom, this works well in my program and im ending with the first row without extra commas.

Really appreciate!