DATA Step, Macro, Functions and more

Replace blank with a specific character

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Replace blank with a specific character

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;


Accepted Solutions
Solution
‎08-25-2016 10:07 AM
Super User
Super User
Posts: 7,067

Re: Replace blank with a specific character

[ Edited ]
Posted in reply to connquest

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

 

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Replace blank with a specific character

Posted in reply to connquest

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

Solution
‎08-25-2016 10:07 AM
Super User
Super User
Posts: 7,067

Re: Replace blank with a specific character

[ Edited ]
Posted in reply to connquest

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

 

New Contributor
Posts: 3

Re: Replace blank with a specific character

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;

Super User
Super User
Posts: 7,067

Re: Replace blank with a specific character

Posted in reply to connquest

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.

New Contributor
Posts: 3

Re: Replace blank with a specific character

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. 

 

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 385 views
  • 1 like
  • 3 in conversation