PROC JSON Hierarchical File Creation Question

[robagomez]

Hello everyone

I've been trying to create a JSON file to match a CMS file layout for provider data like below:

My results look like:

{
    "npi": "1013XXXXXX",
    "type": "GROUP",
    "group_name": "PROVIDER GROUP",
    "addresses": [
      {
        "npi": "1013XXXXXX",
        "address": "1st St",
        "city": "ALBUQUERQUE",
        "state": "NM",
        "zip": "87532",
        "phone": "5559999999"
      }
    ],
    "Plans": [
      {
        "plan_id": "999999999",
        "plan_id_type": "HIOS-PLAN-ID",
        "network_tier": "PREFERRED",
        "years": 2020
      }
    ],
    "last_updated_date": "2019-11-01"
  },

My code to produce the above results is:

%macro npimacro2;
	
		%do i=1 %to &npicount;
	
			write open object;
			write values "npi" "&&NPIVar&i";
			write values "type" "GROUP";
			write values "group_name" "&&Provider&i";
			write values "addresses";
			write open array;
				export work.npic (where=(npi="&&NPIVar&i") keep=npi address city state zip phone);
			write close;
			write values "Plans";
			write open array;
			export work.npiplans;
			write close;
			write values "last_updated_date" "2019-11-01";
			write close;			
		%end;
%mend npimacro2;


proc json out='\\SERVER\File\JSONTest.json' nosastags pretty;
   write open array;*Top Level;
    %npimacro2;
   write close;*Close Top Level;
run;

Questions are:

1. I use the npi variable in my export statement under the "addresses" array but I don't know how to remove the [NPI] variable from the results (the npi is absent from the CMS layout). How is this removed in the program (can't use drop)? Or is there a better way to program it?

2. In the CMS layout it states that the variable "years" is an array. "Years" is only a numeric field in my data and I'm not sure how that is done. I tried various methods like trying to do another "write open array" below "Plans" open array already there but the results didn't come out right.

I appreciate any help. I have never had to create a JSON file before. I'm sooo close though.

Thank You

[BillM_SAS]

I think that the program I wrote showing how to create hierarchical JSON will be helpful to you. In it, I solved the problem you had with the 'npi' variable repeating in a child hierarchy. I created with PROC SQL each set of child data in a separate data set (see the createLowLevelDataSet macro). Once all the needed data sets were created, a DATA step iterated through the data creating the various PROC JSON statements needed to create the desired JSON output. I think you can use this technique in your code to get what you want.

As for the "year" array, adding this array to the "plans" is just another level of hierarchy.  The hierarchy I coded to was:
 Region -+
         |
         +- Subsidiary -+
                        |
                        +- Product -+
                                    |
                                    +- <product data>
It appears that your hierarchy is a little different:

Header-+

       |

       +- Addresses

       +- Plans -+

                 |

                 +- Years