topic Re: TAB delimited file Inserts Spaces in Empty Cells in SAS Programming

TAB delimited file Inserts Spaces in Empty Cells

Balli — Thu, 20 Jul 2023 04:40:58 GMT

Hi,

I am trying to export some data into a TAB delimited file and it inserts space into every empty cell. How can we get rid of that space?

Code:

proc sql;
	create table test as select
	 AccountID
	,'P' 							as STATUS
	,'' 							as AMOUNT				
	,'' 							as REASON_TO_REPORT
	from  XYZ
quit;

data _null_; 
    set test; 
      file "&Out_File_Location./abc.txt"	dlm='	';
   	  if _n_=1 then put 'A	B	C	D   X';

      put   ACCOUNTID STATUS AMOUNT REASON_TO_REPORT; 
 run;

Results(Notepad++)

Re: TAB delimited file Inserts Spaces in Empty Cells

Oligolas — Thu, 20 Jul 2023 08:39:01 GMT

Hi,

you could simply use proc export

PROC EXPORT DATA=test(rename=(ACCOUNTID=A STATUS=B AMOUNT=C REASON_TO_REPORT=X))
   OUTFILE="&Out_File_Location./abc.txt"
   DBMS=TAB REPLACE;
   PUTNAMES=YES;
RUN;

Re: TAB delimited file Inserts Spaces in Empty Cells

mkeintz — Thu, 20 Jul 2023 10:09:17 GMT

Try

dlm='09'x

so that that your SAS source editor doesn't parse the quoted tab character as simple white space.

Re: TAB delimited file Inserts Spaces in Empty Cells

Ksharp — Thu, 20 Jul 2023 11:35:48 GMT

Add DSD option in FILE statement.

file "c:\temp\abc.txt" dlm='09'x dsd;

Re: TAB delimited file Inserts Spaces in Empty Cells

Tom — Thu, 20 Jul 2023 12:28:14 GMT

That is what the DSD option on the FILE statement is for. It will write missing values as no characters at all. It will also add quotes around any value that contains the delimiter (and given that your source file is full of literal tab characters there is definitely a non-zero chance that one or more of you variable values will actually end up containing tab characters).

Also: Do not insert literal TAB characters into code. They will not survive editing. And they make the code un-readable by humans.

What do you want when the dataset TEST is empty? Currently your code will write nothing to the file. Do you want it to write the header line even when the file is empty? If so you need to move that PUT statement BEFORE the SET statement.

Example:

data test ;
  accountid=17484;
  status='P';
  amount=.;
  reason_to_report=' ';
  keep accountid status amount reason_to_report;
run;

filename out temp;

data _null_; 
  file out dsd dlm='09'x;
  if _n_=1 then put 'A' '09'x 'B' '09'x 'C' '09'x 'D' '09'x 'X';
  set test; 
  put ACCOUNTID  AMOUNT REASON_TO_REPORT STATUS; 
run; 

data _null_;
  infile out;
  input;
  list;
run;

And you can see that the tabs are there and nothing is present for the missing values.

104  option generic;
105  data _null_;
106    infile out;
107    input;
108    list;
109  run;

NOTE: The infile OUT is:
      (system-specific pathname),
      (system-specific file attributes)

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

1   CHAR  A.B.C.D.X 9
    ZONE  404040405
    NUMR  192939498

2   CHAR  17484...P 9
    ZONE  333330005
    NUMR  174849990
NOTE: 2 records were read from the infile (system-specific pathname).
      The minimum record length was 9.
      The maximum record length was 9.

Re: TAB delimited file Inserts Spaces in Empty Cells

Balli — Thu, 20 Jul 2023 23:40:04 GMT

Thanks Tom for the detailed explanation.

Re: TAB delimited file Inserts Spaces in Empty Cells

Balli — Thu, 20 Jul 2023 23:41:11 GMT

Thank Ksharp for your reply. It works 🙂