Parse Json to SAS table

[Pandu2]

Hi all,
Does anyone help me out on how to parse a json file and convert it to sas table.
Thanks.

[andreas_lds]

Please use the search-function, this has been asked and answered to many times to repeat it again.

[Pandu2]

I apologise I've seen those posts but all are based using json engine but in my sas eg it is not working got errors like JSON ENGINE CANNOT BE FOUND. So, are there any methods to parse a json file and Create a sas table. Thankyou.

[Ksharp]

If you don't have JSON engine due to sas version then DATA STEP come to rescue .

[Pandu2]

Could you please give me an example so that I can able to do mine. Thankyou

[Pandu2]

Actually so far I tried to add a json data into sas table without the delimiters but afterwards couldn't able to fetch the required fields from it and put it into another table. Can anyone help me in that. Thankyou.

[Tom]

It makes a big difference what exactly you are trying to do.

Please show examples of the data you have and what you need to create from it.

How is the JSON text stored now?

What is the structure of the JSON?

What part of the information in the JSON do you need to extract?  How do you want that stored in SAS?

Plus if the JSON engine is not working for you then it sounds like you might be using a very old version of SAS.  Check what version of SAS you are running. For example by checking the value of the automatic macro variable SYSVLONG.  Do you have access to a more recent version of SAS? Something released by SAS in the last year or two?

[Pandu2]

Thanks for responding, I've a nested json data which has multiple records. Yes, I can show you some examples but this isn't accepting the data as it it is showing as spam when I upload a piece of data. I've a json text file and I used data step to add that long record into a single column of a sas table. From that I would like to fetch some data and put it into a final sas table. I've no clue why json engine isn't working on my sas eg though it was 8.3 version 64 bit but I just checked the custom version information by running PROC PRODUCT_STATUS;RUN;. I got 9.4 maintenance 3.

[Tom]

The version of Enterprise Guide makes no difference as that is just a front end to help you create and submit SAS code to an actual copy of SAS running somewhere else. SAS 9.4m3 is only 6 years old and 5 years out of date.   Since SAS is a subscription license you are paying for the new version but not getting the benefits of using it. Probably would be worth your time to figure out how to get access to a new version. Check with your support team.  Send them this link so they can see how far behind you are.

https://blogs.sas.com/content/iml/2013/08/02/how-old-is-your-version-of-sas-release-dates-for-sas-so...

JSON is just text, so no need to upload anything. Just copy a few examples and paste them into the window that pops up when you click on the Insert Code icon (looks like < / > ).

What is it that you plan to do with the JSON?  Does the JSON text already have the key variables you will need to link the values stored in it back to the values you already have in SAS datasets?  Or will you need to remember the key fields from your SAS dataset in addition the JSON text you stuffed into a character variable?

[Pandu2]

We literally don't have much time to get the access for newer version as the deadline will end today. I used the data step code to get the json text to a single column. Below is the code I used :
data WORK.JSONDATA_TO_TABLE(where=(KEY_P IS NOT MISSING));
infile sresp dsd lrecl=30000000 dlm='[{}}';
input json : $2000.@@;
do i=1 to countw(json,',');
KEY_P=scan(json,i,',');
output;
end;
drop json i;
So it created a temp table having a single column named KEY_P it has the json data as value. I need to pick some data from that temp table to create a final sas table which has the columns of picked data from json data.

[Tom]

That is going to lose a lot of the nesting, if that is important.

Trying build a generic JSON parser with a data step is too large a task.

Instead if the you know what JSON fields you need to find you can probably pretty easily build tool to pull that information out.  Most JSON text is stored as KEY:VALUE pairs.  So you want to find the places with the KEY that you are looking for and parse out the VALUE to but it into a SAS dataset.  If the data is nested you might be able to flatten the data by remembering the values of the keys set at higher levels.

[Pandu2]

I got you it has key value pairs like dictionary and I know what json fields I wanna find and put those values into a dataset. The data has plenty of records. Let's me consider some random data as example.
{ ITEMS
[
{ id - random number of length 4
UN - random number of length 9
These two are unique one's which has corresponding data
{
Ty - random number of length 1
PN - alphanumeric
Dep- character data }}]
{
Ty- random number of length 1
PN- alphanumeric
Dep- character data}
.
.
.
.
ITEMS
[
{
Id - random number of length 4
UN - random number of length 9
{
Ty-random number of length 1
PN- alphanumeric
Dep-character data}}]
.
.
.
.
Here id, un are unique and different for each ITEM field so what I need is I need to pick each item field's id & un and their corresponding values into a sas dataset. likewise it has more ITEM fields and their corresponding values. Thankyou.

[Pandu2]

{

"TotalRecords": 2,

"Items": [

{

"CreatedUTC": "2021-10-29T12:37:38.400Z",

"Dep": "OP - 064913505 - RAN: 61 WEER STRT - Shi To Consr",

"cu": "5690bf70-834a-4",

"cude": "27e3c27b-5878",

"Items": [

{

"Oty": 1,

"PN": "Wl78a35605",

"Oty": 1,

"PrAttrs": {

"Desc": "Ges CAÉ REOR DISPEN",

"IsBatch": false,

"IsInvCtrl": true,

"IsSerialized": false,

"NonTaxable": false,

"PrdT": "1001"

}

}

],

"EDUTC": "2021-10-18T17:00:00.000Z",

"LN": "105132",

"Lo": "1001",

"ModifiedUTC": "2021-10-29T12:37:38.400Z",

"Status": "1001",

"ID": "1074",

"UN": "10712107"

},
In this I need ID,UN,DEP,EDUTC,PN,TY.

[Tom]

If the data is a neat as that then it should not be that hard, although you might need to figure out how to handle repeated values.

First let's convert your example text back into a file so I have something to code with.

options parmcards=json;
filename json temp;

parmcards4;
{
"TotalRecords": 2,
"Items": [
{
"CreatedUTC": "2021-10-29T12:37:38.400Z",
"Dep": "OP - 064913505 - RAN: 61 WEER STRT - Shi To Consr",
"cu": "5690bf70-834a-4",
"cude": "27e3c27b-5878",
"Items": [
{
"Oty": 1,
"PN": "Wl78a35605",
"Oty": 1,
"PrAttrs": {
"Desc": "Ges CAÉ REOR DISPEN",
"IsBatch": false,
"IsInvCtrl": true,
"IsSerialized": false,
"NonTaxable": false,
"PrdT": "1001"
}
}
],
"EDUTC": "2021-10-18T17:00:00.000Z",
"LN": "105132",
"Lo": "1001",
"ModifiedUTC": "2021-10-29T12:37:38.400Z",
"Status": "1001",
"ID": "1074",
"UN": "10712107"
},
;;;;

Now let's read it in as NAME/VALUE pairs per line and look for the names of interest.  Let's keep the DEP value until we see a new one and use it as the key to transpose from tall to wide.

data tall ;
  infile json dlm=' ,:{}[]' truncover ;
  input name :$32. value $200.;
  name=dequote(name);
  value=dequote(value);
  if name = 'Dep' then dep=value ;
  retain dep;
  if name in ('PN','EDUTC','ID','UN','TY') then output ;
run;

proc print data=tall;
run;
proc transpose data=tall out=wide;
  by dep notsorted;
  id name;
  var value;
run;
proc print data=wide;
run;

Results: TALL

Obs    name     value                                              dep

 1     PN       Wl78a35605                  OP - 064913505 - RAN: 61 WEER STRT - Shi To Consr
 2     EDUTC    2021-10-18T17:00:00.000Z    OP - 064913505 - RAN: 61 WEER STRT - Shi To Consr
 3     ID       1074                        OP - 064913505 - RAN: 61 WEER STRT - Shi To Consr
 4     UN       10712107                    OP - 064913505 - RAN: 61 WEER STRT - Shi To Consr

Wide

Obs                           dep                           _NAME_        PN                 EDUTC               ID        UN

 1     OP - 064913505 - RAN: 61 WEER STRT - Shi To Consr    value     Wl78a35605    2021-10-18T17:00:00.000Z    1074    10712107

[Pandu2]

I can't be thankful enough for your assistance. I believe you missed out the TY column in the output.