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"
}
]
not sure if it could work.
make a format for it>
proc format;
value $fmt
' '='null' ;
run;
......
format status $fmt. ;
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.
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.
just the solution I was looking for. Worked like a charm! Thanks Tom 🙂
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: