Proc json produces extra blanks after applying a format

Question

I would like to export a sas dataset to json. I need to apply the commax10.1 format to make it suitable for some language versions. The problem is that the fmtnumeric option applies the format correctly but inserts extra blanks inside the quotes. I have tried trimblanks and other options but have not been able to get rid of them. How to delete the empty blanks inside the quotes? Note: I would like the values to remain inside the quotes

In addition, is it possible to replace the null values with “” ?

Sample data:

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:	est.json' pretty fmtnumeric nosastags trimblanks keys;
export testdata_;
run;

In the link you can see what the output looks like.

output of json

user_1249962 · Answer

Use a custom format function that strips the leading and trailing spaces.

Example:

proc fcmp outlib=work.custom.formatfunctions;
  function stripcommax(number) $;
    return (strip(put(number,commax10.1)));
  endsub;
run;

options cmplib=(work.custom);

proc format;
  value commaxstrip other=[stripcommax()];
run;

data testdata_;
input var1 var2 var3;
datalines;
 3.1582 0.3 1.8
 21 . .
 1.2 4.5 6.4
;
proc json out = 'test.json' 
pretty 
fmtnumeric 
nosastags 
keys 
/*
/* trimblanks */
;
format var: commaxstrip.;
export testdata_;
run;

data _null_;
  infile 'test.json';
  input;
  put _infile_ ;
run;

user_5342700 · Answer

trimblanks only trims trailing blanks. The format itself is adding leading blanks, and proc json has no option that I am aware of to remove leading blanks.

One option would be to convert all of your values to strings, then export.

data testdata_;
    input var1 var2 var3;
    format _all_ commax10.1;

    array var[*] var1-var3;
    array varc[3] $;

    do i = 1 to dim(var);
        varc[i] = strip(compress(put(var[i], commax10.1), '.') );
    end;

    keep varc:;

    datalines;
3.1582 0.3 1.8
21 . .
1.2 4.5 6.4
;

This would be a great feature request. I recommend posting this to the SASWare Ballot Ideas or contact Tech Support and let them know of this issue.

user_66696 · Answer

This is not the only issue with proc json - other challenges include line length limits for a SAS 9 _webout destination, proc failures when ingesting invalid characters, and inability to mod to a destination.

For that reason in the SASjs framework and Data Controller we tend to revert to a data step approach.

Our macro is open source and available here: https://core.sasjs.io/mp__jsonout_8sas.html

To send formatted values, invoke as follows:

data testdata_;
input var1 var2 var3;
format _all_ commax10.1;
datalines;
 3.1582 0.3 1.8
 21 . .
 1.2 4.5 6.4
;
filename _dest "/tmp/test.json";

/* open the JSON */
%mp_jsonout(OPEN,jref=_dest)
/* send the data */
%mp_jsonout(OBJ,testdata_,jref=_dest,fmt=Y)
/* close the JSON */
%mp_jsonout(CLOSE,jref=_dest)

/* display result */
data _null_;
  infile _dest;
  input;
  putlog _infile_;
run;

Which gives:

Proc json produces extra blanks after applying a format

3 Answers