CODEWITHSUNDEEP
exportsasdelimiter
RamB
RamB

Reputation: 428

SAS changing null to ''

I'm trying to export a dataset into a txt file using ; as the delimiter, the dataset has lots of null numeric and char values, I want to replace all those values to '' (no spaces here).

I tried using the code below to do this but it seems SAS will change '' to ' ' (one blank)

if missing(VAR22) then VAR22='' ;
put VAR22 $ @;

Full code:

data _null_;
set  lib.TABLE;                                  
file "/home/user/file.txt" delimiter=';';
format VAR1 $1. ;
format VAR2 $8. ;
format VAR3 $3. ;
format VAR4 $6. ;
format VAR5 $9. ;
format VAR6 $2. ;
format VAR7 $1. ;
format VAR8 $4. ;
format VAR9 $1. ;
format VAR10 $1. ;
format VAR11 BEST12.;
format VAR12 NUMX13.2;
format VAR13 BEST12.;
format VAR14 NUMX13.2;
format VAR15 $1. ;
format VAR16 $2. ;
format VAR17 $3. ;
format VAR18 $3. ;
format VAR19 $4. ;
format VAR20 $20. ;
format VAR21 $1. ;
format VAR22 $1. ;

if missing(VAR1) then VAR1='' ;
put VAR1 $ @; 
put VAR2 $ @;
put VAR3 $ @;
put VAR4 $ @;
put VAR5 $ @;
put VAR6 $ @;
put VAR7 $ @;
put VAR8 $ @;
put VAR9 $ @;
if missing(VAR10) then VAR10='' ;
put VAR10 @;
put VAR11 @;
put VAR12 @;
put VAR13 @;
put VAR14 @;
put VAR15 $ @;
put VAR16 $ @;
put VAR17 $ @;
put VAR18 $ @;
put VAR20 $ @;
put VAR19 $ @;
put VAR21 $ @;
if missing(VAR22) then VAR22='' ;
put VAR22 $ @; 
put ;
run;

I've also tried to use the options missing='', but it didn't work.

Thanks!

Upvotes: 0

Views: 3283

Answers (2)

Joe
Joe

Reputation: 63424

SAS does not support null strings; any number of spaces in a character variable with no other character is by definition a character missing. All strings are padded to their full length (whatever is allocated for that entire column) with '20'x (normal space character), and cannot be zero length.

Depending on what you are doing with these variables, there are ways to get them to not affect your downstream processes. For example, trimn will return a zero length string if the variable is all spaces; so var0=trimn(var1)||trimn(var2)||trimn(var3) will return var1||var3 if var2 has only spaces.

If you are doing concatenation, even better is to use cats instead of the || operator, as it automatically removes spaces (including removing all spaces from a missing string). The resulting variable must contain spaces, but the components have theirs removed.

The idea of concatenating strings together is also a good one. Unfortunately I don't think CATX works with completely missing strings, but you could do this perhaps in a macro or something.

data _null_;
set test;
file "c:\temp\test.csv";
outvar = catt(trimn(x1),';',trimn(x2),';',trimn(x3));
put outvar;
run;

Finally, if you're going to be interfacing with another DBMS that does have the concept of a null string, there may be a better solution; post those details.

Upvotes: 1

dataNinja124
dataNinja124

Reputation: 130

Try something like this:

data one;
   length VAR1-VAR4 $1;
   input VAR1-VAR4;
   datalines;
A B C D
E . F G
H . . J
;
run;

data two;
   set one;
   SP=';';
   test1=trim(left(VAR1)) || sp || trim(left(VAR2)) || sp || trim(left(VAR3)) || sp || 
      trim(left(VAR4));
   test1 = compress(test1,' ');
run;

proc print data=two;
run;

I suggest you build a simple macro to run through all your VAR1 -- VARXX.

If you look at the variable test1 you can see thats what you want to output to your text file.

Note, If any of your char variables already contain spaces as part of thier actual value, this code will remove them. If that is an issue, let me know and i can fix it.

Upvotes: 1

Related Questions