topic Re: proc json handling of empty values in SAS Programming

proc json handling of empty values

noffer — Tue, 04 Jun 2024 19:30:33 GMT

I've created a json procedure that I've optimized and takes the fields as an parameter to the shared macro. It's been working great, until today.

Today I learned that data may flow into this logic with NULL values. However, the data get's passed as "addressLine1":"", instead of "addressLine1": null. Is there an option that I can add that will make this translation? None of these appear to do the trick.

		proc json out=request nopretty NOSASTAGS;
		   	write open object;
				write value &batch_object.;
			   	write open object;
					write values "batchID" "&batchID.";
					write value "batch";
				   	write open array;
			         	export jsonData&threadId. (keep= &json_keep_fields.);
				   	write close;
			   	write close;
		   	write close;
		run;

Re: proc json handling of empty values

noffer — Tue, 04 Jun 2024 20:26:28 GMT

FWIW, I found this statement "By default, SAS writes an empty string ("") to the JSON output file when a SAS data set character variable contains a missing value." here which is somewhat confusing, considering the statement above. That says you can use the NULL keyword when using the write values statement. So I have to do some crazy if/else statement to generate a null data element? Seems hacky.

Re: proc json handling of empty values

Tom — Wed, 05 Jun 2024 00:04:09 GMT

I think they mean that it will write that empty quoted string for an empty character variable. Which is consistent with how SAS treats such values. In SAS character variables are fixed length and padded with spaces to fill out the full storage length. So trailing spaces are meaningless. And a character variable that is completely filled with only spaces is treated as MISSING. So there is no difference between an empty string and a NULL value. (Or for that matter between an empty string and a string of any length that is only spaces).

If you need the JSON text to have the keyword NULL instead then you will need to use a WRITE VALUE statement which is where the keyword NULL is part of the syntax (just like TRUE and FALSE are part of the syntax).

In general I have found it much easier to just use a data step to write the JSON file. Then you can control exactly what is written. And you can use BY group processing to help you generate any nested structures in the JSON structure you want to create.

Re: proc json handling of empty values

noffer — Wed, 05 Jun 2024 14:16:02 GMT

Thanks for the reply. Its really quite unfortunate as empty numeric values look fine in the json. I'm looking into this open source macro https://core.sasjs.io/mp__jsonout_8sas.html , which may do the trick too, but now there's concerns about using "open source code", and we'll have to re-test a ton of work as a number of processes use the shared logic.

Re: proc json handling of empty values

Tom — Wed, 05 Jun 2024 14:13:35 GMT

If the logic in the macro works for you then you could always just remove/replace the linkages it has to other macros in that system. Then tweak its inputs to match how your current system works. Then you would only need to test that your modified macro performs in the same way and not need to re-test your other existing programs.

From a quick glance it does appear to have thought about most of the issues involved in making JSON files (how to quote special characters etc.)