This is my 1st time I am using PROC JSON, I have used the below code to get JSON output
data have;
infile datalines delimiter=',';
input user $ org $ acct $ city $;
datalines;
John,Sales,saving,london
Mary,Sales,saving,london
kim,Sales,saving,london
sanya,Sales,saving,london
;
Proc sql;
create table user as
select distinct user
from have;
quit;
proc transpose data=user out=have_1_row(drop=_name_ _LABEL_) let;
var user;
run;
Proc sql;
create table org as
select distinct org
from have;
quit;
proc transpose data=org out=have_2_row(drop=_name_ _LABEL_) let;
var org;
run;
Proc sql;
create table acct as
select distinct acct
from have;
quit;
proc transpose data=acct out=have_3_row(drop=_name_ _LABEL_) let;
var acct;
run;
Proc sql;
create table city as
select distinct city
from have;
quit;
proc transpose data=city out=have_4_row(drop=_name_ _LABEL_)let;
var city;
run;
proc json out="/sasdata/incoming/vexiere/TESTE_NOV.JSON" nosastags pretty;
%let originator=users;
write open object;
write values "&originator";
write open array;
write open array;
write open object;
WRITE VALUES "users";
EXPORT have_1_row / NOKEYS;
WRITE VALUES "org";
EXPORT have_2_row / NOKEYS;
WRITE VALUES "acct";
EXPORT have_3_row / NOKEYS;
WRITE VALUES "city";
EXPORT have_4_row / NOKEYS;
write close;
write close;
write close;
write close;
run;
*********************OutPut JSON File ****************************************;
{
"users": [
[
{
"users": [
"John",
"Many",
"Kim",
"Sanya"
],
"organizationId": [
"Sales"
],
"accountId": [
"saving"
],
"TenantId": [
"london"
]
}
]
]
}
*********************OutPut JSON File Expected****************************************;
My output is very close but not as expected.
{
"users": [
[
{
"userIds": [
"John",
"Many",
"Kim",
"Sanya"
],
"org":
"Sales"
,
"acct":
"saving"
,
"city":
"london"
}
]
]
}
Any help will is highly appreciated.
Thanks
Jack
I solved a similar problem in this post. As noted there, the EXPORT statement places the data into a JSON container. Since you do not want that behavior, I recommend using a DATA Step to create the needed PROC JSON statements and then running that code.
My version is too old to run this proc, but have you tried different options such as KEYS?
How do you expect SAS to come up with string userIds ?
Should JSON create arrays for single values?
If worse comes to worst you can always post-process the output file to match your exact requirements.
I cannot tell from your post which is the output that you want and which is the output that you got.
I cannot get your PROC JSON code to run.
1762 proc json out=json nosastags pretty; 1763 write open object; 1764 write values "&originator"; 1765 write open array; 1766 write open array; 1767 write open object; 1768 WRITE VALUES "users"; 1769 EXPORT have_1_row / NOKEYS; 1770 WRITE VALUES "org"; 1771 EXPORT have_2_row / NOKEYS; ERROR: A string is required at this point in the text so that an object key may be emitted. 1772 WRITE VALUES "acct"; 1773 EXPORT have_3_row / NOKEYS; 1774 WRITE VALUES "city"; 1775 EXPORT have_4_row / NOKEYS; 1776 write close; 1777 write close; 1778 write close; 1779 write close; 1780 run; ERROR: Due to the previous error, the JSON output file is incomplete and invalid, or in some cases, unable to be created. If created, the JSON output file is retained so that you can review it to help determine the cause of the error. NOTE: PROCEDURE JSON used (Total process time): real time 0.01 seconds cpu time 0.00 seconds
Please post the desired output. Make sure to use the Insert Code button to get a pop-window to paste the lines of text so the forum doesn't try to convert them into word processed paragraphs.
Explain how the JSON output relates the SAS dataset you started with. Also what is all of that stuff in the middle that makes all of those other datasets that are never used?
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!
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.