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;
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
Since SAS does not have NULL are you refering to missing numerics or missing character variables or both?
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
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;
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.
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.