CODEWITHSUNDEEP
sas
Leksa99
Leksa99

Reputation: 95

Why is the last character getting removed after applying tranwrd function

I want to replace certain values in my json file (in this example null values with empty quotation marks.) My solution is working correctly but, for some mysterious reason, the last character of the json file is deleted. Regardless of the last character, the code always deletes it - I have also tried with a different json file that ends in curly braces.

What is causing this and more importantly how can I prevent this?

data testdata_;
input var1 var2 var3;
format _all_ commax10.1;
datalines;
 3.1582 0.3 1.8
 21 . .
 1.2 4.5 6.4
;
proc json out = 'G:\test.json' pretty fmtnumeric nosastags keys;
export testdata_;
run;

data _null_;
  infile 'G:\test.json';
  file 'G:\test.json';
  input;
  _infile_ = tranwrd(_infile_,'null','""');
  put _infile_ ;
run;

To see how the contents change, first run the code until "data null" statement and check the file content, then run the last statement.

Upvotes: 0

Views: 175

Answers (2)

Tom
Tom

Reputation: 51611

One strange thing is that the PROC JSON always writes a text file that uses LF as the end of line characters.

So you might be able to get your overwriting of the file to work if add these caveats:

  1. Use TERMSTR=LF on the INFILE statement.
  2. Use SHAREDBUFFERS on the INFILE statement.
  3. Replace the string with the same number of bytes with the TRANWRD() function and not put a space as the last character on the line.

I would also search for ': null' instead of just 'null' to reduce risk of replacing those characters in some other string in the file.

data _null_;
  infile json SHAREBUFFERS termstr=lf ;
  file json ;
  input ;
  _infile_ = tranwrd(_infile_,': null',':   ""');
  put _infile_;
run;

Upvotes: 1

Joe
Joe

Reputation: 63434

Data _null_ has it correct; don't write to the same file. SAS offers this option, but in the modern day it's almost always the wrong answer, due to how SAS supports this and the fact that storage is sufficiently cheap and fast.

In this case, it looks like it's a relatively easy fix, but you probably should do as suggested and write to a new file anyway - there will be other issues.

data testdata_;
input var1 var2 var3;
format _all_ commax10.1;
datalines;
 3.1582 0.3 1.8
 21 . .
 1.2 4.5 6.4
;
proc json out = 'H:\temp\test.json' pretty fmtnumeric nosastags keys;
export testdata_;
run;

data _null_;
  infile 'H:\temp\test.json' end=eof;
  file 'H:\temp\test.json';
  input @;
  putlog _infile_;
  _infile_ = tranwrd(_infile_,'null','""  ');
  
  len = length(_infile_);
  put _infile_ ;
  if eof then put _infile_;
run;

There's two changes. One, I use '"" ' instead of '""' in the tranwrd; that's because otherwise you end up with slightly odd results with new lines being added. If your JSON parser doesn't like "" ,, then you may want to instead have two tranwrd, one for null, and one for null, or something similar (or use a regular expression). But what's important is the number of characters needs to match in the input and the output. If you can't handle that (like the extra spaces are problematic) then you're left with "write a new file".

Two, I look for the end of the file, then intentionally write out a second line there. That avoids the issue you're having with the bracket, as it avoids having the EOF being written out before the bracket. I'm not 100% sure I know why you need that - but you do.

Another option, which might make more sense, is to only write the lines that have the bracket.

data _null_;
  infile 'H:\temp\test.json' sharebuffers; 
  file 'H:\temp\test.json';
  input @;
  putlog _infile_;
  if find(_infile_,'null') then do;
    _infile_ = tranwrd(_infile_,'null','"" ');  
    put _infile_;
  end;
run;

I added sharebuffers because that should make it run a bit faster. Note that I also remove one space - something weird about how SAS does this seems to otherwise remove a space from the following line otherwise. No idea why, probably something weird with EOL characters.

But again - don't do any of this unless there's no other option. Write a new file.

Upvotes: 2

Related Questions