topic Re: JSON creation from a SAS dataset in SAS Programming

JSON creation from a SAS dataset

Pallab_2110 — Mon, 16 Nov 2020 07:39:25 GMT

hi All,

I would like to get an output like this JSON format.

{
"source":{
"name":"SCBSG"
},
"customer":{
"name":"010000000009568G",
"custom_fields":[
{
"name":"cashone",
"value":"Y"
},
{
"name":"ccft",
"value":"N"
},
{
"name":"wealth",
"value":"N"
}
]
}
}

{
"source":{
"name":"SCBSG"
},
"customer":{
"name":"0100000000035987K",
"custom_fields":[
{
"name":"cashone",
"value":"Y"
},
{
"name":"ccft",
"value":"N"
},
{
"name":"wealth",
"value":"N"
}
]
}
}

The SAS dataset sample looks like below:

customer Cashone CCFT wealth
010000000009568G Y N Y
0100000000035987K Y N Y

and I would like to create a JSON file as per the above format from this dataset. I tried to use the following script, but it is giving me a different output than I needed.

proc json out="sample.json" pretty nosastags;
	write open array;

		write open object;
		write values "source";
			write open array;
			write values "name" "SCBSG";
			write close;	
		write values "customer";
			write open array;
			export samp;
			write values "name" customer;
			write values "custom_fields";
				write open object;
					write open array;
					write values "name" "cashone";
					write values "value" cashone;
					write close;
					write open array;
					write values "name" "ccft";
					write values "value" ccft;
					write close;
					write open array;
					write values "name" "wealth";
					write values "value" wealth;
					write close;
				write close;
			write close;
		write close;			
	write close;
run;

This is giving me an output like this:

[
{
"source": [
"name",
"SCBSG"
],
"customer": [
{
"source": "SCBSG",
"customer": "010000000009568G",
"Cashone": "Y",
"CCFT": "N",
"wealth": "Y"
},
{
"source": "SCBSG",
"customer": "0100000000035987K",
"Cashone": "Y",
"CCFT": "N",
"wealth": "Y"
},

]

}
]

can anyone please help me with the sas script to be followed? Many thanks.

@sasjson

Regards,

Re: JSON creation from a SAS dataset

Tom — Mon, 16 Nov 2020 17:00:47 GMT

Your requested output does not look like valid JSON to me. You don't have any outer container.

But here is a data step that should create the output like your example (only formatted for easier human readablity).

data _null_;
  set have;
  put '{"source":{"name":"SCBSG"}'
    / ',"customer":' 
    / '  {"name":' customer :$quote. 
    / '  ,"custom_fields":'
  ;
  length name $32 sep $1 value $200 ;
  sep='[';
  do name='cashone','ccft','wealth';
    value = vvaluex(name);
    put @5 sep $1. '{"name":' name :$quote. ',"value":' value :$quote. '}' ;
    sep=',';
  end;
  put @5 ']' / @3 '}' / '}' ;
run;

Results:

{"source":{"name":"SCBSG"}
,"customer":
  {"name":"010000000009568G"
  ,"custom_fields":
    [{"name":"cashone" ,"value":"Y" }
    ,{"name":"ccft" ,"value":"N" }
    ,{"name":"wealth" ,"value":"Y" }
    ]
  }
}
{"source":{"name":"SCBSG"}
,"customer":
  {"name":"0100000000035987K"
  ,"custom_fields":
    [{"name":"cashone" ,"value":"Y" }
    ,{"name":"ccft" ,"value":"N" }
    ,{"name":"wealth" ,"value":"Y" }
    ]
  }
}

To have it write the results to a file instead of the SAS log just add a FILE statement to the data step.

And it is not hard to modify it to wrap all of those JSON strings into a single object by adding commas and brackets.

Code:

data _null_;
  set have end=eof;
  if _n_=1 then put '[' @;
  else put ',' @;
  put '{"source":{"name":"SCBSG"}'
    / ' ,"customer":' 
    / @4 '{"name":' customer :$quote. 
    / @4 ',"custom_fields":'
  ;
  length name $32 sep $1 value $200 ;
  sep='[';
  do name='cashone','ccft','wealth';
    value = vvaluex(name);
    put @6 sep $1. '{"name":' name :$quote. ',"value":' value :$quote. '}' ;
    sep=',';
  end;
  put @6 ']' / @4 '}' / @2 '}' ;
  if eof then put ']';
run;

Results:

[{"source":{"name":"SCBSG"}
 ,"customer":
   {"name":"010000000009568G"
   ,"custom_fields":
     [{"name":"cashone" ,"value":"Y" }
     ,{"name":"ccft" ,"value":"N" }
     ,{"name":"wealth" ,"value":"Y" }
     ]
   }
 }
,{"source":{"name":"SCBSG"}
 ,"customer":
   {"name":"0100000000035987K"
   ,"custom_fields":
     [{"name":"cashone" ,"value":"Y" }
     ,{"name":"ccft" ,"value":"N" }
     ,{"name":"wealth" ,"value":"Y" }
     ]
   }
 }
]

Re: JSON creation from a SAS dataset

BillM_SAS — Mon, 16 Nov 2020 22:21:27 GMT

This looks like JSONL. You should probably read this blog by @ChrisHemedinger.

Re: JSON creation from a SAS dataset

Pallab_2110 — Tue, 17 Nov 2020 07:02:09 GMT

Hi Tom,
Many thanks for your response. Can I check, if I dont need the line breaks in between the rows in my output, how can i achieve the same? Sorry, the requirement changed a bit and I am trying to explore how can I get rid of the line breaks. So currently, the output file looks like this:

{"source":{"name":"SCBSG"}
,"customer":
{"name":"010000000009568G"
,"custom_fields":
[{"name":"cashone" ,"value":"Y" }
,{"name":"ccft" ,"value":"N" }
,{"name":"wealth" ,"value":"Y" }
]
}
}
{"source":{"name":"SCBSG"}
,"customer":
{"name":"0100000000035987K"
,"custom_fields":
[{"name":"cashone" ,"value":"Y" }
,{"name":"ccft" ,"value":"N" }
,{"name":"wealth" ,"value":"Y" }
]
}
}
Instead of this, I would like the output to be like this:
{"source":{"name":"SCBSG"},"customer":{"name":"010000000009568G","custom_fields":[{"name":"cashone","value":"Y"},{"name":"ccft","value":"N"},{ "name":"wealth","value":"Y"}]}}
{"source":{"name":"SCBSG"},"customer":{"name":"0100000000035987K","custom_fields":[{"name":"cashone","value":"Y"},{"name":"ccft","value":"N"},{ "name":"wealth","value":"Y"}]}}

Is this possible to be achieved?