Proc JSON

[Mike69]

How do you use Proc JSON to export data if you want to group variables from a data set into separate containers?  How do you join multiple data sets into single JSON records?  For example, what if you had a list of demographic information for thousands of sales managers and those sales managers were related to another data set with possibly thousands of different customers (like with a sales manager ID)?  The goal is to create one JSON record for each sales manager with the demographic information from one data set with their customers from another data set imbedded into a container within that same Json record.

[BillM_SAS]

I think this code could give you what you want. For each sales manager there is a container that includes the sale manager information and a container of all the customers for that sales manager.

To make it work means generating code for the JSON procedure based on the data in the sale manager data set. To do that, macro code is used to create the JSON procedure code for each sales manager and the customers of the sales manager. This allows for exporting the data from the data sets for each sales manager.

I have included some sample data sets to use with the code to demonstrate producing the JSON I think you described.

data work.manager;
input id     1. 
      name   $  3-5 
      region $  7-11;
datalines;
1 Joe North
2 Sam South
3 Sue East
4 Joy West
;;;;
run; 
proc print data=work.manager; run;

data work.customers;
input managerID  1. 
      customerID     3-5 
      name       $   7-12 
      address    $  14-29; 
datalines;
4 101 Semis  29 Bay Ct.
3 102 Jackem 17 2nd St.
2 103 VWs    567 Maple St.
1 104 Yankee 123 East Ave.
3 105 Helion 47 Willow Ct.
3 106 Bellis 707 Coast Dr.
4 107 Audi   987 Oak St.
1 108 Socks  654 Weston Ave.
;;;;
run; 
proc print data=work.customers; run;


/* 
   JSON procedure code for getting a manager and the
   customer for that manager.
*/
%macro managersCustomers(managerID);
write open array; /* container for the sales manager */
export work.manager (where=(id=&managerID));
write open array; /* container for the customers */
export work.customers (where=(managerID=&managerID));
write close;      /* container for the customers */
write close;      /* container for the sales manager */
%mend managersCustomers;

/* Loop through all the manager IDs. */
%macro managerRecords;
%do i = 1 %to &dim_IDs;
   %managersCustomers(&&&managerID_&i);
   %end;
%mend managerRecords;

/* 
   Produce a macro variable for each manager ID and the
   total count of managers in the data set. 
*/
proc sql;
select id into :managerID_1 - :managerID_&SysMaxLong from work.manager;
%let dim_IDs = &sqlObs;
quit;

/* 
   JSON procedure code for getting all the managers and their customers. 
   The resultant JSON output is stored in the salesManagers.json file 
   in the current location.
*/
proc json out="salesManagers.json" pretty nokeys nosastags;
  write open array; /* container for all the data */
    %managerRecords;
	write close;    /* container for all the data */
run;