topic Re: Replace blank with a specific character in SAS Programming

Replace blank with a specific character

connquest — Mon, 22 Aug 2016 20:25:32 GMT

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

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;

Re: Replace blank with a specific character

ballardw — Mon, 22 Aug 2016 22:35:19 GMT

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

Re: Replace blank with a specific character

Tom — Tue, 23 Aug 2016 00:23:35 GMT

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

Re: Replace blank with a specific character

Tom — Tue, 23 Aug 2016 01:19:50 GMT

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.

Re: Replace blank with a specific character

connquest — Thu, 25 Aug 2016 13:55:35 GMT

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.

Re: Replace blank with a specific character

connquest — Thu, 25 Aug 2016 14:06:51 GMT

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;