Hi all,
Does anyone help me out on how to parse a json file and convert it to sas table.
Thanks.
Please use the search-function, this has been asked and answered to many times to repeat it again.
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?
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.
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?
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.