proc json export boolean

[Edoedoedo]

Hi, I have a dataset with a column "flag". This flag contains a boolean flag, and I can make it in any way I want: a 0/1 number, a "true" "false" string, whatever.

Then, I have to export this dataset to a json file. Mandatory requirement: the flag field must be output as a true boolean as per json specification:

[
  {
    "flag": true
  },
  {
    "flag": false
  }
]

With:

proc json out=...;
  export dataset;
run;

if "flag" is coded as a numeric 0/1, I get of course:

[
  {
    "flag": 1
  },
  {
    "flag": 0
  }
]

 which is incorrect;

if "flag" is coded as a string "true"/"false", I get of course:

[
  {
    "flag": "true"
  },
  {
    "flag": "false"
  }
]

which is incorrect.

How can I achieve the json output with a true boolean value as per json specification?

Thank you.

[BillM_SAS · Accepted solution]

The SAS data set only supports character and numeric data. The problem you are encountering with the PROC JSON EXPORT statement is that the procedure cannot export Boolean data because there no Boolean data in the data set. You have data that can be interpreted as Boolean data, but the JSON procedure only understands SAS numeric and character data.

What you need to do is write SAS code to interpret the data for PROC JSON. Instead of the PROC JSON EXPORT statement, you will need to write the data with the PROC JSON WRITE statements. Here is some code to show you what needs to be done. I am using a data set with 2 observations of 3 variables, a numeric, character, and a numeric that can be interpreted as a Boolean. The code I am including uses a DATA step to create the needed PROC JSON statements in a SAS program that will then be run after the DATA step.

%let sourceDataSet=work.test;

data &sourceDataSet;
charData = "Testing some Boolean output";
numData=17;
booleanData=0;
output;
charData = "Testing more Boolean output";
numData=24;
booleanData=1;
output;
run;

proc print data=work.test; run;

/*****************************
* Modifiable macro variables
******************************/
%let jsonProcCodeSpec=./sasuser/jsonProcCode.sas;
%let jsonOutputSpec=./sasuser/jsonOutput.txt;


/*****************************
* Constant macro variables
******************************/
%let stmtEnd=%STR(;);


/* Write the custom PROC JSON code based on the values in the data set. 
   This will be run after the DATA Step creates it. */
data _null_;
  /* specifies the output file for PUT statements                      */
  FILE "&jsonProcCodeSpec" DISK;
  set work.test end=last; 

  if _N_ eq 1 
    then do;
      /* Only on the first observation in the data set, write the required 
         initial statements to the JSON procedure code file.                */
      put "proc json pretty out=""&jsonOutputSpec"" nosastags &stmtEnd";
      put "write open array &stmtEnd /* open outermost object */";
	  end;
  /* Open the JSON object for the current data set observation */
  put "write open object&stmtEnd     /* open  data observation object */";
  /* Write data set character value to JSON */
  put "write value ""charData"" """charData""" &stmtEnd";
  /* Write data set numeric value to JSON */
  put "write value ""numdata"" " numdata "&stmtEnd";
  /* Write JSON Boolean based on the data set "Boolean" value (in this case, 0 or 1). */
  if (booleanData EQ 0)
    then put "write value ""booleanData"" false &stmtEnd ";
    else put "write value ""booleanData"" true  &stmtEnd ";
  /* Close the JSON object for the current data set observation */
  put "write close &stmtEnd          /* close data observation object */";
  if last
    then do;
      /* Only on the last observation in the data set, write the required 
         final statements to the JSON procedure code file.                */
      put "write close &stmtEnd      /* close outermost object */";
      put "run &stmtEnd";
    end;
run;

/* Now run the generated custom JSON procedure code to produce the 
   JSON formatted output file of the data set.                         */
%include "&jsonProcCodeSpec";

Here is the generated PROC JSON code:

proc json pretty out="./sasuser/jsonOutput.txt" nosastags ;
write open array ; /* open outermost object */
write open object;     /* open  data observation object */
write value "charData" "Testing some Boolean output " ;
write value "numdata" 17 ;
write value "booleanData" false ; 
write close ;          /* close data observation object */
write open object;     /* open  data observation object */
write value "charData" "Testing more Boolean output " ;
write value "numdata" 24 ;
write value "booleanData" true  ; 
write close ;          /* close data observation object */
write close ;      /* close outermost object */
run ;

Here is the resultant JSON file:

[
  {
    "charData": "Testing some Boolean output",
    "numdata": 17,
    "booleanData": false
  },
  {
    "charData": "Testing more Boolean output",
    "numdata": 24,
    "booleanData": true
  }
]

This should give you enough information to modify this program to your needs.

[Edoedoedo]

Thank you very much!

I hoped there could have been a more direct way, but as you said since there is no boolean column type I guess there is no other way but manual.

[BillM_SAS]

I initially thought it was easier as well. You can suggest it as a new feature via the SASware ballot.

[Tom]

You don't need to use PROC JSON to write such a simple JSON file.  Much easier to just write the JSON directly instead of trying to write the PROC JSON code.  You can just use a FORMAT to have your boolean values displayed as true/false.

proc format ;
  value tf ._ - .Z,0 = 'false' other='true' ;
run;

data example;
  infile cards dlm='|' dsd truncover ;
  input charData :$30. numData booleanData ;
  format booleanData tf. ;
cards;
Testing some Boolean output|17|0
Testing more Boolean output|24|1
;

filename json temp;
data _null_;
  set example end=eof;
  file json ;
  if _n_=1 then put '['/' ' @; else put ',' @;
  put '{' @;
  put '"charData": ' chardata :$quote. ;
  put ' ,"numData": ' numData ;
  put ' ,"booleanData": ' booleanData ;
  put ' }';
  if eof then put ']';
run;

Plus you end up with much prettier formatting of the JSON file where continuation characters are at the beginning of the lines where it is easier for humans scanning the file to see them.

[
 {"charData": "Testing some Boolean output"
 ,"numData": 17
 ,"booleanData": false
 }
,{"charData": "Testing more Boolean output"
 ,"numData": 24
 ,"booleanData": true
 }
]