Generate a json file

[Jose7]

Hi experts,

I need to generate a json file from a table like this

ACCOUNTS	DATA
account1	data1
account1	data2
account1	data3
account2	data1
account2	data2
account3	data1
account3	data2
account3	data3

but, I want to generate 1 json file for each account with its own data, example:

{account1, "data1", "data2", "data3"}   --> first json file

{account2, "data1", "data2"}  --> second json file

{account3, "data1", "data2", "data3"}   --> third json file

Any idea how to do it ?

[Tom · Accepted solution]

Just forget that the files are JSON and instead think of the problem as generating multiple text files.

To do that use the FILE statement with the FILEVAR= option so that you can write to different files based on the value of variable.

But first let's see how to generate your example files:

data have;
  input ACCOUNT $ DATA $ ;
cards;
account1 data1
account1 data2
account1 data3
account2 data1
account2 data2
account3 data1
account3 data2
account3 data3
;

data _null_;
  set have;
  by account;
  if first.account then put '{' account @ ;
  put ',' data :$quote. @;
  if last.account then put '}' ;
run;

Results

{account1 ,"data1" ,"data2" ,"data3" }
{account2 ,"data1" ,"data2" }
{account3 ,"data1" ,"data2" ,"data3" }

NOTE: Those lines don't really look like valid JSON, but it is what you asked for.

Now to write them to separate files just add a variable with the filename and a FILE statement with the FILEVAR= option.

data _null_;
  set have;
  by account;
  length filename $256 ;
  filename=cats('c:\downloads','\',account,'.json');
  file json filevar=filename;
  if first.account then put '{' account @ ;
  put ',' data :$quote. @;
  if last.account then put '}' ;
run;

[Jose7]

Thks! Tom, nice help!