topic Re: Dynamically writing SAS variable values as key value pairs to JSON file using PUT statements in SAS Programming

Dynamically writing SAS variable values as key value pairs to JSON file using PUT statements

jazz17 — Mon, 06 Feb 2017 22:16:05 GMT

Hi,

I am writing to JSON file using SAS dataset as input using below code:

data _null_;

file _webout PS=32767 ;

set &indata end=lastrec;

put ' "header":{' /; /*header starts*/

%do i = 1 %to 14;

put ' "' @; put "%trim(&&var&i)"@; put '":"'@; put &&var&i '",';

%end;

put ' "' @; put "%trim(&&var15)"@; put '":"' &&var15'"';

put ' },' /; /*header ends*/

run;

In the output message, it puts and extra space for variable value when writing to file. Please see output file below:

"header":{

"senderMessageId":"senderMessageId ",

"senderMessageName":"getDecision ",

"versionNumber":"1 ",

"sequenceNumber":"1 ",

"senderMessageIdEcho":" ",

"sessionId":"16f526829a0f48f29a6a144d2f1bcd9d ",

"creationTimeStamp":"01JAN60:00:00:00 ",

"senderApplicationId":"ZT ",

"senderSubApplicationId":" ",

"senderHostName":"eai-CDEV.com ",

"billingAU":". ",

"customerId":"12345 ",

"teamMemberId":" ",

"environment":"CDEV ",

"applicationId":" "

I do not need this extra space at end of value for each variable value. Also, it puts two spaces for blank character variables, I do not need that. I want nothing to be written to file when character or numeric variable is missing. Please help on this if someone has faced similar issue.

Thanks!

Re: Dynamically writing SAS variable values as key value pairs to JSON file using PUT statements

Reeza — Mon, 06 Feb 2017 22:28:53 GMT

If all your variables are the same type using an array may be easier than a macro.

Add an if condition to only put values if not missing. The MISSING() function will work on character or numeric variables.

if not missing(var) then do;
put...;
end;

To remove space after variable name, back up the point using another PUT or adding it to your statement.

See the solution here:

https://communities.sas.com/t5/General-SAS-Programming/How-to-remove-trailing-blanks-when-PUT-statement-resolves-for/m-p/190655/highlight/true#M7261

put variable +(-1);

Re: Dynamically writing SAS variable values as key value pairs to JSON file using PUT statements

LaurieF — Mon, 06 Feb 2017 22:40:16 GMT

When you put a variable, rather than a character string, and it doesn't have an specific format, it always suffixes it with a space. The way I always do it is to use the pointer code +(-1).

Put that after the value &&var&i, and things should be different, if not spot on.

Also you can check the value of &&var&i with the missing function. Like:

if not missing(&&var&i) then do; 
   put…

Re: Dynamically writing SAS variable values as key value pairs to JSON file using PUT statements

jazz17 — Wed, 08 Feb 2017 21:03:01 GMT

Thank you Reeza! This worked! 🙂

Re: Dynamically writing SAS variable values as key value pairs to JSON file using PUT statements

jazz17 — Wed, 08 Feb 2017 21:03:59 GMT

Thank you Laurie! This worked 🙂

Re: Dynamically writing SAS variable values as key value pairs to JSON file using PUT statements

BillM_SAS — Thu, 09 Feb 2017 22:27:28 GMT

FYI - If you are running SAS 9.4, the JSON procedure can write SAS data to a JSON file.