topic Re: Json in SAS Programming

Parse Json to SAS table

Pandu2 — Mon, 22 Nov 2021 12:08:40 GMT

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

Re: Json

andreas_lds — Mon, 22 Nov 2021 10:12:14 GMT

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

Re: Json

Pandu2 — Mon, 22 Nov 2021 12:02:41 GMT

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.

Re: Json

Ksharp — Mon, 22 Nov 2021 12:23:53 GMT

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

Re: Json

Pandu2 — Mon, 22 Nov 2021 13:07:39 GMT

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

Re: Json

Pandu2 — Mon, 22 Nov 2021 13:16:46 GMT

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.

Re: Json

Tom — Mon, 22 Nov 2021 16:32:37 GMT

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?

Re: Json

Pandu2 — Tue, 23 Nov 2021 00:35:37 GMT

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.

Re: Json

Tom — Tue, 23 Nov 2021 01:25:46 GMT

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-software.html

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?

Re: Json

Pandu2 — Tue, 23 Nov 2021 01:39:39 GMT

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.

Re: Json

Tom — Tue, 23 Nov 2021 01:47:50 GMT

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.

Re: Json

Pandu2 — Tue, 23 Nov 2021 02:07:22 GMT

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.

Re: Json

Pandu2 — Tue, 23 Nov 2021 02:18:22 GMT

{

"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.

Re: Json

Tom — Tue, 23 Nov 2021 05:56:21 GMT

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

Re: Json

Pandu2 — Tue, 23 Nov 2021 06:05:22 GMT

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

Re: Json

Pandu2 — Tue, 23 Nov 2021 06:13:36 GMT

And also in EDUTC just need date only not the entire value. Thanks.

Re: Json

Pandu2 — Tue, 23 Nov 2021 06:33:20 GMT

I just checked out this code unfortunately the table has no values in it like you have.

Re: Json

Tom — Tue, 23 Nov 2021 12:05:33 GMT

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

The output is based on the input. Since there were no TY tags in the input there was no values output for. If you require an empty variable just create that along the way.

Re: Json

Tom — Tue, 23 Nov 2021 12:06:41 GMT

@Pandu2 wrote:
And also in EDUTC just need date only not the entire value. Thanks.

If you want to do that type of modification do it after you have gotten the text out the JSON and into actual variables.

Re: Parse Json to SAS table

Ksharp — Tue, 23 Nov 2021 12:11:36 GMT

data temp;
infile 'c:\temp\a.json' dsd recfm=n dlm='{[]}, ';
input have : $200. @@;
if not missing(have);
run;

data want;
merge temp temp(firstobs=2 rename=(have=_have)); 
if prxmatch('/\b(EDUTC|TY|Dep|PN|ID|UN)\b/i',have);
run;