topic Re: Export to CSV with spaces for empty strings in SAS Programming

Export to CSV with spaces for empty strings

i_Van — Tue, 20 Oct 2020 12:30:31 GMT

I need to export a table to csv having all cells filled either with data either with space, but SAS does not keep character field with single space and on export I receive:

"","","A","5"

instead of wanted:

" "," ","A","5"

The table has more than 500 columns so defining format would be a bad idea.

Re: Export to CSV with spaces for empty strings

ballardw — Tue, 20 Oct 2020 14:15:25 GMT

Please at least show how you are generating the CSV file. What you show is not typical for many CSV files.

And why is a format a bad idea?

Re: Export to CSV with spaces for empty strings

i_Van — Tue, 20 Oct 2020 14:23:30 GMT

the initial dataset is downloaded from oracle database, (the table has more than 500 columns and I'm not sure that no new columns would be inserted - so that's why I think that directly insert FORMAT is not a good idea) then some magic and I need to export the resulting dataset.

I'm using:

%ds2csv(data=Basis_upd, runmode=b, sepchar=3B, csvfile=&export_dir.\&date_exp._Basis_update.csv);

to fill in empty fields I've tried

data Basis_upd;
   set Basis_upd;
   array change $ _character_;
        do over change;
            if missing(change) then change=' ';
        end;
 run ;

that code correctly fills in empty cells if I use then change='tst'; , but if I try to use then change='<space>'; the cell remains empty, not holding single space.

Re: Export to CSV with spaces for empty strings

Tom — Tue, 20 Oct 2020 14:30:00 GMT

@i_Van wrote:

I need to export a table to csv having all cells filled either with data either with space, but SAS does not keep character field with single space and on export I receive:

"","","A","5"

instead of wanted:
,,A,5
The table has more than 500 columns so defining format would be a bad idea.

Please explain more about why you need this (how are you planning to use the resulting file) and what methods you tried to generate the file.

In a normal CSV file quotes are only needed around values that contain the delimiter or the quote character. So a line to represent those four values would look like this:

,,A,5

You can force SAS to add quotes around values by using the ~ modifier in the PUT statement.

Example:

data have;
  infile cards dsd truncover ;
  length var1-var4 $10;
  input var1-var4 ;
cards;
,,A,5
;

217   data _null_;
218     set have;
219     file log dsd;
220     put (var1-var4) (~) ;
221   run;

" "," ","A","5"

Re: Export to CSV with spaces for empty strings

Tom — Tue, 20 Oct 2020 14:32:25 GMT

SAS stores character strings as fixed length. So values are padded with spaces to their full assigned length. It will ignore trailing spaces in comparisons and treat a string of all spaces as "missing". So your DO loop is doing nothing.

Re: Export to CSV with spaces for empty strings

Tom — Tue, 20 Oct 2020 15:17:11 GMT

There is no need to use a macro to write a delimited text file. Just write your own data step(s) and then you can have full control. So first get a list of the variables.

proc contents data=Basis_upd noprint out=contents; run;
proc sort; by varnum; run;

Then you can use that data to write the header line. If you want to add quotes around both the numeric and character variables then just use _all_ variable list:

put (_all_) (~) ;

And if you only want it to add the quotes around the character variables you can also use that data to generate the PUT statement with the ~ modifiers.

Example:

filename csv "&export_dir.\&date_exp._Basis_update.csv";
filename putstmt temp;
data _null_;
   set contents end=eof;
   file csv dsd dlm=';' lrecl=1000000 ;
   put name ~ @;
   file putstmt lrecl=75 ;
   if _n_=1 then put 'put ' @;
   put name @ ;
   if type=2 then put '~ ' @ ;
   if eof then put ';'
 run;

Then you can use another data step to write the lines of data.

data _null_;
   set Basis_upd ;
   file csv dsd dlm=';' lrecl=1000000 mod;
 %include putstmt ;
 run;

Re: Export to CSV with spaces for empty strings

i_Van — Tue, 20 Oct 2020 15:16:14 GMT

Please explain more about why you need this (how are you planning to use the resulting file) and what methods you tried to generate the file.
In a normal CSV file quotes are only needed around values that contain the delimiter or the quote character.

The "perfect" format of CSV is required for further data transfer - for upload to next system. I just have demand to store each value in double quotes and fill all blank strings with single space.

Thank you - the variant below worked, can you kindly help how to export column names? the code below exports just data without first row having column names

data _null_;
    set Basis_upd;
	file "&export_dir.&date_exp.\&date_exp._Basis_update_.csv" dlm='3B'x dsd ;
    put (AUSPLATZ_UNTERSICHERH_ID--KOMMENTAR) (~) ;
   run;

Re: Export to CSV with spaces for empty strings

Tom — Tue, 20 Oct 2020 16:11:42 GMT

I am interested in learning what tools are requiring such formatting, especially if it is a popular tool like a database or other analysis software.

See my other post with details of how to get the names. If you want to add quotes around all of the variables then you can simplify by using PROC TRANSPOSE to get the names. But that will not give you the type of the variables.

proc transpose data=Basis_upd(obs=0) out=names; run;
filename csv "&export_dir.&date_exp.\&date_exp._Basis_update_.csv";
data _null_;
  set names;
  file csv dlm=';' dsd lrecl=1000000 ;
  put _name_ ~ @ ;
run;

data _null_;
  set Basis_upd;
  file csv dlm=';' dsd  mod lrecl=1000000 ;
  put (_all_) (~);
run;