topic Re: PROC JSON - How do I enclose only a few variables' values into brackets [ ] ? in SAS Programming

PROC JSON - How do I enclose only a few variables' values into brackets [ ] ?

InspectahDex — Thu, 30 Dec 2021 19:45:29 GMT

I have a dataset that I'm exporting out into a JSON file using PROC JSON.

My current export code:

proc json out="&Results_Path./ProviderDirectory_%sysfunc(date(),yymmddN8.).json" pretty nosastags;
export &GV_Curr_YYYYMMM.;
run;

What do I need to add to my code so that I can select a few variables with their values enclosed in brackets? I want the other non-selected variables to be bracket-free and as is.

Example:

"specialties": ["Cardiology"],

"languages_spoken": ["Mandarin","English"]

"provider_ethnicity": [""],

"provider_race": [""],

Re: PROC JSON - How do I enclose only a few variables' values into brackets [ ] ?

Tom — Thu, 30 Dec 2021 21:04:50 GMT

I don't understand. In your example all of the list of values have brackets around them.

What is the structure of your SAS dataset?

Re: PROC JSON - How do I enclose only a few variables' values into brackets [ ] ?

InspectahDex — Thu, 30 Dec 2021 21:11:15 GMT

Ah, let me clarify:

This is what I have:

[
{
"plan_type": "Medical",
"provider_type": "Facility",
"provider_group_affiliation": "Group ABC",
"provider_group_website": "",
"TIN": 123456789,
"clinic_name": "Medical Clinic",
"clinic_address": "123 Fake St",
"clinic_city": "Orange",
"clinic_state": "CA",
"clinic_zipcode": 92539,
"clinic_phone_number": 8008881000,
"provider_name": "",
"providerNPI": 1234567890,
"provider_category": "Other",
"specialties": "Cardiology",
"languages_spoken": "",
"accepting": "",
"telehealth_available": "",
"accessibility": "",
"auxiliary_aids_and_services": "",
"cultural_competency_training": "",
"provider_ethnicity": "",
"provider_race": "",
"last_credential_dt": null
},

And this is what I want:

[
{
"plan_type": "Medical",
"provider_type": "Facility",
"provider_group_affiliation": "Group ABC",
"provider_group_website": "",
"TIN": 123456789,
"clinic_name": "Medical Clinic",
"clinic_address": "123 Fake St",
"clinic_city": "Orange",
"clinic_state": "CA",
"clinic_zipcode": 92539,
"clinic_phone_number": 8008881000,
"provider_name": "",
"providerNPI": 1234567890,
"provider_category": "Other",
"specialties": ["Cardiology"],
"languages_spoken": [""],
"accepting": "",
"telehealth_available": "",
"accessibility": "",
"auxiliary_aids_and_services": "",
"cultural_competency_training": "",
"provider_ethnicity": [""],
"provider_race": [""],
"last_credential_dt": null
},

Re: PROC JSON - How do I enclose only a few variables' values into brackets [ ] ?

InspectahDex — Thu, 30 Dec 2021 21:12:34 GMT

Basically I need brackets around the values for specialties, languages_spoken, provider_ethnicity, and provider_race in JSON output

Re: PROC JSON - How do I enclose only a few variables' values into brackets [ ] ?

Tom — Thu, 30 Dec 2021 21:14:07 GMT

But neither of those is a SAS dataset.

What is the SAS dataset that you want to use to create the JSON file?

Will all of the lists implied by the brackets only contain one item? If there are more than one item then how are storing that information in the SAS dataset? SAS datasets do not have lists.

Re: PROC JSON - How do I enclose only a few variables' values into brackets [ ] ?

InspectahDex — Thu, 30 Dec 2021 21:46:06 GMT

The dataset looks something like the table below. There are more observations (~3000). If a value has more than one value within a cell, then it's treated as one value - meaning, a value of "Cardiology, Oncology" would need to be exported into ["Cardiology, Oncology"] on the JSON file.

plan_type

provider_type

provider_group_affiliation

provider_group_website

TIN

clinic_name

clinic_address

clinic_city

clinic_state

clinic_zipcode

clinic_phone_number

provider_name

providerNPI

provider_category

specialties

languages_spoken

accepting

telehealth_available

accessibility

auxiliary_aids_and_services

cultural_competency_training

provider_ethnicity

provider_race

last_credential_dt

Medical

Facility

Group ABC

123456789

Medical Clinic

123 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456790

Medical Clinic

124 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456791

Medical Clinic

125 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456792

Medical Clinic

126 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456793

Medical Clinic

127 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456794

Medical Clinic

128 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456795

Medical Clinic

129 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456796

Medical Clinic

130 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456797

Medical Clinic

131 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456798

Medical Clinic

132 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456799

Medical Clinic

133 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456800

Medical Clinic

134 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456801

Medical Clinic

135 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456802

Medical Clinic

136 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456803

Medical Clinic

137 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456804

Medical Clinic

138 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456805

Medical Clinic

139 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456806

Medical Clinic

140 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456807

Medical Clinic

141 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456808

Medical Clinic

142 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456809

Medical Clinic

143 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456810

Medical Clinic

144 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456811

Medical Clinic

145 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456812

Medical Clinic

146 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456813

Medical Clinic

147 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456814

Medical Clinic

148 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456815

Medical Clinic

149 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Medical

Facility

Group ABC

123456816

Medical Clinic

150 Fake St

Orange

92539

8008881000

Other

Cardiology

null

Re: PROC JSON - How do I enclose only a few variables' values into brackets [ ] ?

Tom — Thu, 30 Dec 2021 22:27:56 GMT

Someone else with have to explain how to use PROC JSON. I find it too confusing. It is much easier to just use a data step to write JSON text.

First let's make some actual sample SAS data.

data have;
  infile cards dsd dlm='|' truncover ;
  input (TIN plan_type provider_type provider_group_affiliation languages_spoken ) (:$100.) ;
cards;
123456789|Medical|Facility|Group ABC|English
123456790|Medical|Facility|Group ABC|English,Spanish
123456791|Medical|Facility|Group ABC|
;

Notice how the LANGUAGE_SPOKEN data is using comma as delimiter between the individual values.

Now let's make a data step to write the JSON.

I will put in a branch to handle some variables like LAGUAGES_SPOKEN differently.

filename json temp;

data _null_;
  length name $32 value item $200;
  set have end=eof;
  file json;
  if _n_=1 then put '['  @;
  else put ',' @;
  sep='{' ;
  do name='TIN','plan_type','provider_type','provider_group_affiliation','languages_spoken';
     value=vvaluex(name);
     put @2 sep $1. name :$quote.  ':'@;
     sep = ',';
     if name not in ('languages_spoken') then put value :$quote. @;
     else do;
       item = scan(value,1,',');
       put '[' item :$quote. @;
       do index=2 to countw(value);
          item=scan(value,index,',');
          put ',' item :$quote. @;
       end;
       put ']' @ ;
     end;
     put;
  end;
  put ' }' ;
  if eof then put ']';
run;

Results:

[{"TIN" :"123456789"
 ,"plan_type" :"Medical"
 ,"provider_type" :"Facility"
 ,"provider_group_affiliation" :"Group ABC"
 ,"languages_spoken" :["English" ]
 }
,{"TIN" :"123456790"
 ,"plan_type" :"Medical"
 ,"provider_type" :"Facility"
 ,"provider_group_affiliation" :"Group ABC"
 ,"languages_spoken" :["English" ,"Spanish" ]
 }
,{"TIN" :"123456791"
 ,"plan_type" :"Medical"
 ,"provider_type" :"Facility"
 ,"provider_group_affiliation" :"Group ABC"
 ,"languages_spoken" :["" ]
 }
]