Hi, I'm working on a conference paper on copying data between SAS and JSON. For reading JSON files into SAS, I started with the examples in the LIBNAME Statement: JSON Engine documentation and I'm using SAS 9.4TS1M4.
What I've seen about reading JSON files into SAS generally involves looking at the data and then writing code specific to the data layout. I wonder if there's a way to automate the process.
For example, the section on Merging Data Sets shows how to read JSON code into multiple SAS data sets and then use ordered values (variables beginning with ORDINAL_) to merge the data sets into a single data set. Using that JSON file to save space here, could I automate that? Maybe after the PROC DATASETS in the documentation, I could do something like the following:
1. Read the dictionary.columns table for the library containing the component data sets and subset to obtain all variables that start with ORDINAL_.
proc sql noprint;
create table ordinals as
select memname, name
from dictionary.columns
where libname="IN"
and upcase(name) like 'ORDINAL_%';
quit;
Resulting data set:
Obs memname name
1 INFO ordinal_root
2 INFO ordinal_info
3 INFO_ADD ordinal_info
4 INFO_ADD ordinal_add
5 INFO_NAME ordinal_info
6 INFO_NAME ordinal_name
7 ROOT ordinal_root
2. Remove observations for ORDINAL_ values that only occur once since those are at the bottom of the hierarchy.
proc sort data=ordinals nouniquekey;
by name;
run;
Resulting data set:
Resulting data set:
Obs memname name
1 INFO ordinal_info
2 INFO_ADD ordinal_info
3 INFO_NAME ordinal_info
4 INFO ordinal_root
5 ROOT ordinal_root
3. Construct the merge by working down the levels from ORDINAL_ROOT.
a. Merge 1 is for data sets with ORDINAL_ROOT (INFO and ROOT).
b. Since INFO from Merge 1 also has ORDINAL_INFO, Merge 2 is the data set created in Merge 1 with INFO_ADD and INFO_NAME (they both have ORDINAL_INFO).
c. Additional levels could work the same way.
That worked with a few JSON files but then I got to a few fairly simple cases that didn't. For example, consider the following JSON file (simplified to remove additional blocks of rows like these).
[
{
"country": "usa",
"city": "chicago",
"income": {
"salary": {
"salary_h1": 35,
"salary_h2": 45
},
"stocks": {
"stocks_h1": 8,
"stocks_h2": 12
}
},
"tax": {
"incometax": {
"incometax_estimated": 3,
"incometax_withheld": 7
},
"salestax": 5,
"cartax": {
"cartax_h1": 2,
"cartax_h2": 2
}
}
}
]
Using PROC SQL and PROC SORT as above, this generated the following result, where there's no connection between ORDINAL_INCOME and the rest of the tree.
Obs memname name
1 INCOME_SALARY ordinal_income
2 INCOME_STOCKS ordinal_income
3 ROOT ordinal_root
4 TAX ordinal_root
5 TAX ordinal_tax
6 TAX_CARTAX ordinal_tax
7 TAX_INCOMETAX ordinal_tax
So I hit a dead end with this effort, unless there's a way to identify some subset of JSON files that'll work this way.
Has anyone done in any research in this area and come up with anything interesting?
Thanks,
Bruce
Click image to register for webinar
