Export SAS dataset to JSON with missing string variable as null

[tedway]

How do I export a missing string variable to a json file as null rather than ""?

SAS Code:

data mydata;
INFILE DATALINES DLM='|';
Length CODE 3 Name $ 50;
Input CODE Name;
datalines;
11|Bob 
12|Joe
13|Larry

;
run;

data newdata;
length status $2.;
format status $2.;
informat status $2.;
set mydata;
if name = "Joe" then 
	do;
		status=.;
		code=.;
	end;
	else status='Y';

run;

proc json out="P:\newdata.json" pretty NOSASTAGS;
   export newdata;
run;

Here's what the exported json looks like:

[
  {
    "status": "Y",
    "CODE": 11,
    "Name": "Bob"
  },
  {
    "status": "",
    "CODE": null,
    "Name": "Joe"
  },
  {
    "status": "Y",
    "CODE": 13,
    "Name": "Larry"
  }
]

Here's what I want (i.e. status for Joe should be null rather than "":

[
  {
    "status": "Y",
    "CODE": 11,
    "Name": "Bob"
  },
  {
    "status": null,
    "CODE": null,
    "Name": "Joe"
  },
  {
    "status": "Y",
    "CODE": 13,
    "Name": "Larry"
  }
]

[Ksharp]

not sure if it could work.

make a format for it>

proc format;

value $fmt

 ' '='null' ;

run;

......

format status $fmt. ;

[tedway]

Thanks, but this doesn't seem to work for me. Status is exporting as " ." in the json file but I actually need it to be null.

[Tom]

You can trick it by converting your character variables into numeric variables with formats attached.

Here is a little example that actually dynamically defines the format from the data.

data mydata;
  infile DATALINES dsd DLM='|' truncover;
  length CODE 8 Name $50 Status 8;
  input CODE Name Status;
datalines;
11|Bob|1
12||2
13|Larry|.
;
 
proc sql noprint;
create table names as 
  select put(monotonic(),8.-L) as number,name
  from (select distinct name from mydata
        where not missing(name))
;
create table formats as 
select distinct 'to_char' as fmtname,'N' as type,number as start,name as label
  from names
union 
select distinct 'to_num' as fmtname,'I' as type,name as start,number as label
  from names
;
quit;

proc format cntlin=formats; run;

data for_json;
  set mydata (rename=(name=old_name));
  name=input(old_name,to_num.);
  format name to_char.;
  drop old_name;
run;

filename json temp;

proc json out=json pretty NOSASTAGS fmtnumeric;
   export for_json;
run;

[
  {
    "CODE": 11,
    "Status": 1,
    "name": "Bob"
  },
  {
    "CODE": 12,
    "Status": 2,
    "name": null
  },
  {
    "CODE": 13,
    "Status": null,
    "name": "Larry"
  }
]
NOTE: 17 records were read from the infile JSON.
      The minimum record length was 1.
      The maximum record length was 19.

[rekhi]

just the solution I was looking for. Worked like a charm! Thanks Tom 🙂

[lexjansen]

This is a nice trick, but it is not going to be very efficient for very large datasets with lots of character data. I wish SAS supported null for missing character data in JSON.