Can anyone please tell me how to extract this kind of data :
[{"number":"8457215152","type":"Cell","state":"LA","country":"US","tz":"CT","zip":"70546","msa":"0"},{"number":"4363685555","type":"Cell","state":"LA","country":"US","tz":"CT","zip":"70546","msa":"0"}]
I would like to have result to have something like this for this id
id number type state country tx zip msa 1 845... 1 436...
My problem is some id have more than two numbers ( this id have only 2 number ) I usually am able to use extractvalue function in mysql , but in this case , i'm at the end of my rope.
Thanks
data work.parsed;
infile cards;
input;
length line_str $32000 rec_str $800 number type state country tx zip msa $100 elemname $32;
line_str = compress(_infile_, '"'); /* remove quotes */
line_str = translate(line_str, ':', ','); /* make : a key:value separator */
keep id number type state country tx zip msa;
id = _N_;
rec_count=countc(line_str, '{');
array elem {*} $ number type state country tx zip msa;/* order is important */
put rec_count=;
do r=1 to rec_count;
if r = 1 then rec_start=3;
else rec_start = rec_end + 4;
rec_end = findc(line_str, '}', rec_start) - 1;
rec_str=substr(line_str, rec_start, rec_end - rec_start + 1);
do i=1 to dim(elem);
elemname = vname(elem(i));
elem(i)= scan(rec_str, i * 2, ':');/* this way relying on all elements provided in record in expected order */
if findc(elem(i), '}') > 0 then elem(i) = substr(elem(i), 1, findc(elem(i), '}') - 1);
end;
output;
end;
cards;
[{"number":"8457215152","type":"Cell","state":"LA","country":"US","tz":"CT","zip":"70546","msa":"0"},{"number":"4363685555","type":"Cell","state":"LA","country":"US","tz":"CT","zip":"70546","msa":"2"},{"number":"33333","type":"Cell","state":"CA","country":"US","tz":"CT","zip":"33333","msa":"3"}]
;
run;
Surely this has some assumptions on what data looks like. HTH Vasja