PROC json with no array

[jackpete]

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

[BillM_SAS]

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.

[ChrisNZ]

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.

[Tom]

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?