BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
connquest
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

5 REPLIES 5
ballardw
Super User

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

Tom
Super User Tom
Super User

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
Calcite | Level 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;

Tom
Super User Tom
Super User

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
Calcite | Level 5

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. 

 

 

 

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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