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: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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