BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Jose7
Obsidian | Level 7

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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
Obsidian | Level 7
Thks! Tom, nice help!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 555 views
  • 4 likes
  • 2 in conversation