Really hard to follow what the question is when you hide the details in attachments.
It looks like you just want to extract these "fields" from the JSON data.
Region|Employee|Employee2|Sales|Rank_by_Region
Northeast|Sawyer|Leanne|$2,411,912|1
Northeast|Kelly|Laura|$2,350,720|2
Not sure if you do it with JSON map but just pulling together some of the fields found by the generic mapping I can get something like your table. Not sure if it is pulling the fields you want and whether it is pulling ALL of the values you want.
filename map2 temp;
data _null_;
file map2;
put '
{
"DATASETS": [
{
"DSNAME": "WANT",
"TABLEPATH": "/root/result/data/root/children/children/metrics/Rank by Region",
"VARIABLES": [
{
"NAME": "Region",
"TYPE": "CHARACTER",
"PATH": "/root/result/data/root/children/element/name",
"CURRENT_LENGTH": 12,
"OPTIONS" : ["RETAIN"]
},
{
"NAME": "Last_Name",
"TYPE": "CHARACTER",
"PATH": "/root/result/data/root/children/children/element/formValues/Last Name",
"CURRENT_LENGTH": 11
},
{
"NAME": "First_Name",
"TYPE": "CHARACTER",
"PATH": "/root/result/data/root/children/children/element/formValues/First Name",
"CURRENT_LENGTH": 8
},
{
"NAME": "Sales",
"TYPE": "CHARACTER",
"PATH": "/root/result/data/root/children/children/metrics/Sales/fv",
"CURRENT_LENGTH": 10
},
{
"NAME": "Rank",
"TYPE": "NUMERIC",
"PATH": "/root/result/data/root/children/children/metrics/Rank by Region/rv"
}
]
}
]
}
';
run;
%let path=c:\downloads;
%let fname=ReportResponse.txt;
filename resp "&path/&fname";
libname resp json map=map2 ;
options nocenter ls=132 ps=100; run;
proc print data=resp.want;
run;
First_
Obs Region Last_Name Name Sales Rank
1 Northeast Sawyer Leanne $2,411,912 1
2 Northeast Kelly Laura $2,350,720 2
3 Northeast Yager Beth $2,303,847 3
4 Northeast De Le Torre Sandra $607,895 4
5 Northeast Kieferson Jack $584,933 5
6 Northeast Sonder Melanie $295,108 6
7 Mid-Atlantic Bernstein Lawrence $1,060,632 1
8 Mid-Atlantic Folks Adrienne $1,047,776 2
9 Mid-Atlantic Hollywood Robert $1,026,874 3
10 Mid-Atlantic Brown Vernon $331,735 4
11 Mid-Atlantic Corcoran Peter $325,147 5
12 Mid-Atlantic Ingles Walter $229,439 6
13 Mid-Atlantic Smith Thomas $221,379 7
14 Mid-Atlantic Young Sarah $209,634 8
15 Southeast Strome Fred $595,372 1
16 Southeast Lynch Sam $592,471 2
17 Southeast McClain Sean $531,371 3
18 Southeast Benner Ian $520,737 4
19 Central Torrison Mary $1,690,350 1
20 Central Gale Loren $1,669,290 2
21 Central Ellerkamp Nancy $847,227 3
22 Central Zemlicka George $822,500 4
23 South Pierce Charles $2,084,241 1
24 South Nelson Arthur $1,654,297 2
25 South Conner Beatrice $1,650,742 3
26 Northwest Gedot Harriet $739,741 1
27 Northwest Hall David $513,213 2
28 Northwest Becker Kyle $508,234 3
29 Southwest Bates Michael $1,068,907 1
30 Southwest Bell Caitlin $1,040,481 2
31 Southwest Hunt Matthew $731,413 3
32 Southwest Johnson Andrew $445,052 4
33 Southwest Schafer Rose $408,280 5
34 Web Walker Robert $3,902,762 1
Note that you can also tell it how to process the fields. For example you might want SALES to be numeric instead of character.
{
"NAME": "Sales",
"TYPE": "NUMERIC",
"PATH": "/root/result/data/root/children/children/metrics/Sales/fv",
"INFORMAT" : [ "COMMA", 32, 0 ],
"FORMAT": ["DOLLAR",10,0]
}
