BookmarkSubscribeRSS Feed
robagomez
Calcite | Level 5

Hello everyone

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

 

CMS Provider JSON Sample.PNG

 

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

 

 

 

 

1 REPLY 1
BillM_SAS
SAS Employee

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

 

 

 

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 3970 views
  • 0 likes
  • 2 in conversation