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

[jazz17]

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!

[Reeza · Accepted solution]

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-statem...

put variable +(-1);

[jazz17]

Thank you Reeza! This worked! 🙂

[LaurieF]

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…

[jazz17]

Thank you Laurie! This worked 🙂

[BillM_SAS]

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