Import JSON file into SAS dataset

[gabras]

Hello everyone,

i'm facing some issue in parsing the value stored into a data set column. 

You can see the data in the attacched file.

Here an example: 

{"IdUtente":153592,"CodiceFiscale":"CCCCCCCC","DataUltimaClassificazione":"2019-02-27T16:50:06.810","ClassiUtente":[{"IdClasse":3,"Descrizione":"Superstiti","Semantiche":[{"Codice":"7604","Descrizione":"Familiari superstiti"}]},{"IdClasse":1,"Descrizione":"Famiglia","Semantiche":[{"Codice":"7581","Descrizione":"Famiglia"}]}]}

I would like to have just one data set but any suggestion are well excepted.

Thank you

[Kurt_Bremser]

JSON is pure text. Why do you post an Excel file?

[gabras]

The JSON is the format in which a value is stored in a dataset column, as you can see in the xlsx file i attached.

Think the excel as the export of a sas dataset.

[Kurt_Bremser]

Instead of storing the data into a dataset, read the original file with libname json.

[Tom]

---

@gabras wrote:

The JSON is the format in which a value is stored in a dataset column, as you can see in the xlsx file i attached.

Think the excel as the export of a sas dataset.

---

But why did you go to the trouble of copying the dataset into an XLSX file and uploaded the XLSX as an attachment to your post?

WHy not just dump the text into the SAS log and copy and paste the text into your posting so that everyone can actually read it.

So does each value of the variable constitute a complete JSON file?  Or are these instead individual lines from a JSONL file?  The JSON engine in SAS cannot read JSONL files.  So if you have the latter you will need to add either square or curly brackets around the output and commas between the lines to convert the JSONL lines into an actual JSON file.   If each value is an independent fully formed JSON object then that might not work. In which case you would need to write each line as its own file and then read each one back in.

How complex is the JSON (remember I can't see it since you hid it in a XLSX file so that when I viewed it in the browser it looks like four black horizontal lines)?  If it is a simple format you can probably just parse it instead by looking for the keywords and taking the next token as the value.

[BillM_SAS]

The example JSON data you supplied is hierarchical. The JSON LIBNAME engine flattens the data into, in the case of the example data, 4 SAS data sets. You can write SAS code to take the 4 data sets and produce a single data set that contains the de-normalized data. The JSON  LIBNAME engine documentation contains an example.

[gabras]

Thank you @BillM_SAS ,

so i have to create a json file from the value stored in the dataset column and then i have to read it with the libname statement?

Is there any other way to parse the json value?

Thank you

[andreas_lds]

---

@gabras wrote:

Thank you @BillM_SAS ,

so i have to create a json file from the value stored in the dataset column and then i have to read it with the libname statement?

Is there any other way to parse the json value?

Thank you

---

You could write a parser yourself - ok, that is a bad idea.

I am not that experienced in using json files, so i don't know if the json-libname-engine can do that: you can export all values into one file and read that.

Untested code:

data _null_;
  set datasetWithJsonVariable;
  file "export.json";
  put JsonVariable;
run;

[Ksharp]

You could parse JSON by writing some data step code.

option noquotelenmax;
data temp;
x='{"IdUtente":153592,"CodiceFiscale":"CCCCCCCC","DataUltimaClassificazione":"2019-02-27T16:50:06.810","ClassiUtente":[{"IdClasse":3,"Descrizione":"Superstiti","Semantiche":[{"Codice":"7604","Descrizione":"Familiari superstiti"}]},{"IdClasse":1,"Descrizione":"Famiglia","Semantiche":[{"Codice":"7581","Descrizione":"Famiglia"}]}]}';

id+1;
do i=1 to countw(x,'{}[]');
 temp=scan(x,i,'{}[]');output;
end;
drop x i;
run;
data temp1;
 set temp;
do i=1 to countw(temp,',');
 temp1=scan(temp,i,',');
 if not missing(temp1) then output;
end;
drop temp i;
run;
data want;
 set temp1;
 name=dequote(scan(temp1,1,':','q'));
 value=dequote(scan(temp1,-1,':','q'));
run;

[jv7340]

1) First create an libname

ex:  libname sampA 'C:\Users\abc123\Desktop\folder';

2) Then upload the JSON file to the libname using the json format

ex: libname sampA json 'C:\Users\abc123\Desktop\folder\tools.json';

Hope this helps!