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 ?
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;
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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.