Replace blank with a specific character

[connquest]

I have a SAS dataset that has a couple of columns that are 1 character in length.  When exporting the dataset as a comma delimited file I want to replace NULLS with a single blank space in the txt file.

For example I'm looking for the output to be:

T,S123, , , ,1.2,P

vs

T,S123,,,,1.2,P

I need a single space placed between the commas when data is missing.

I've tried proc export, and it works great except I for the issue I'm having with replacing nulls with a space:

proc export

data = ds.pkts

dbms=csv

label

outfile="C:xyz\file.txt"

replace;

run;

[ballardw]

Since SAS does not have NULL are you refering to missing numerics or missing character variables or both?

[Tom · Accepted solution]

Write the file using a datastep.  Use the DLM option, but without the DSD option.  For numeric variables set the MISSING option to ' '.

options missing=' ';
data _null_;
  set sashelp.class (obs=4) ;
  if _n_=2 then age=.;
  if _n_=3 then sex=' ';
  file log dlm=',';
  put (_all_) (+0) ;
run;

Results:

Alfred,M,14,69,112.5
Alice,F, ,56.5,84
Barbara, ,13,65.3,98
Carol,F,14,62.8,102.5

[connquest]

Thank you. After further review of the file I discovered the columns that needed to contain a single blank space were actually place holders so the entire column was empty.  That made it a little easier since I did need to check if a value was present I just needed to output a black space in the file.  I used your recommendation and modified it to come up with something that worked in this case.  Thanks.

I used something along the following to get the output I needed. The 3rd column contains the single black space.:

ata _null_;

set ds.txns;

file "C:\SASDocs\DEP.tx;

if _n_ = 1 then

/*insert titles*/

put 'title1,title2,title3,title4';

outvar = catt(

    hdr_type,',',

    id,',',

    ' ,',

    amt

)

;

put outvar;

run;

[Tom]

Why do you want a file like that?  Normal CSV file specfications use adjacent delimiters to indicate a missing value. There is no need (or desire) to insert extra unneeded spaces.

Read about the DSD option on the FILE and INFILE statements.

[connquest]

I was writting a SAS script to generat an output file that gets consummed by an existing applicaiton.  I tried standard CSV and that did not work, so after reviewing an older file I was able to track down several columns in the middle that were empty and needed to contian a single space between the commas otherwise the file would not import properly.  I don't have the ability to modify the existing process that consumes the file, so in order for it to work I had to generate the file in the format the orginal developer built the process to support.