Export a dataset to csv inserting the delimiter at the end of each row

[Rabelais]

The question is very simple but I cannot find a working solution: given a generic dataset how to create a csv with header and data rows ending with the delimiter?

Usually to export a dataset to a csv I use this code

%let lib=WORK;
%let mem=have;
%let outfile=;

data have;
input col1 col2 $2.; cards;
1 ab
3 cv
run;

proc export 
data=have
dbms=dlm
outfile="&outfile.";
delimiter=";";
run;

and then the csv contains

col1;col2
1;ab
3;cv

However, what if we need to place the delimiter at the end of each row too? That is, to produce the following 

col1;col2;
1;ab;
3;cv;

I tried using put in a data step, but while it works for the header row, it inserts an extra whitespace before the delimiter in the data rows. Moreover, it is not good since you have to manually write the column names in the put statement.

/* Build the header string in the same order as variables appear in the dataset */
proc sql;
select name into :header separated by ';'
from dictionary.columns
where libname = "%upcase(&lib)" and memname = "%upcase(&mem)"
order by varnum;
quit;

/* Write data to csv */
data _null_;
set have;
file "&outfile." lrecl=32767;
if _n_=1 then put "&header.;";
put col1 ';' col2 ';';
run;

output

col1;col2;
1 ;ab ;
3 ;cv ;

AI suggested this code

/* Write header line, with trailing semicolon */
data _null_;
file "&outfile" lrecl=32767 encoding="utf-8";
put "&header.;"; /* <-- trailing ; */
run;

/* Append data rows, each ending with a trailing semicolon */
data _null_;
set &lib..&mem;
file "&outfile" mod dsd dlm=';' lrecl=32767 encoding="utf-8";
put (_all_) (:+0) ';'; /* <-- trailing ; */
run;

which however places two delimiters at the end of each data row

col1;col2;
1;ab;;
3;cv;;

AI then tried to fix it, but it is not able to find a way.

Any suggestion?

[Ksharp · Accepted solution]

%let outfile=c:\temp\want.csv ;


data have;
input col1 col2 $2.; 
cards;
1 ab
3 cv
;
run;
 
/* Build the header string in the same order as variables appear in the dataset */
proc sql noprint;
select name into :header separated by ';'
from dictionary.columns
where libname = "WORK" and memname = "HAVE"
order by varnum;
quit;

/* Write data to csv */
data _null_;
set have;
file "&outfile." lrecl=32767 dlm=';' dsd;
if _n_=1 then put "&header.;";
put col1  col2 +(-1) ';';
run;

[Rabelais]

Wow what sorcery is this! Many thanks!

[LinusH]

Just to fix the output issue, you could use the of _all_ construct with catx:

data _null_;
	set &Lib..&Mem;
	length out $1000;
	out = cats(catx(';', of _all_),';');
	file "&outfile" mod lrecl=32767 encoding="utf-8";
	put out;
run;

[Tom]

If you are using the DSD option then the way to add an extra delimiter is to write a missing value.

 73         data _null_;
 74           file log dsd dlm=';';
 75           set sashelp.class(obs=3);
 76           _n_=.;
 77           put (_all_ _n_) (:) ;
 78         run;
 
 Alfred;M;14;69;112.5;
 Alice;F;13;56.5;84;
 Barbara;F;13;65.3;98;

The simplest way to get the list of names is using PROC TRANSPOSE with zero observations.  Then you can use that list to generate the header row.  You can then use the MOD option on the FILE statement to append the data lines.

Example:

data test; set sashelp.class(obs=3); run;

proc transpose data=test(obs=0) out=names;
  var _all_;
run;

filename csv temp;
data _null_;
  set names end=eof;
  file csv dsd dlm=';';
  put _name_ @;
  if eof then do; _n_=.; put _n_; end;
run;
data _null_;
  file csv mod dsd dlm=';';
  set test ;
  _n_=.;
  put (_all_ _n_) (:) ;
run;

data _null_;
  infile csv;
  input;
  put _infile_;
run;

Result:

Name;Sex;Age;Height;Weight;
Alfred;M;14;69;112.5;
Alice;F;13;56.5;84;
Barbara;F;13;65.3;98;