Hi all,
I am beginning my second SAS program and have hit a bit of a roadblock. My raw data are in a .txt all on one line, and are grouped together by 5 separate categories: yaxis, xaxis, col, val, and row (example posted below). The .txt has the curly and regular brackets, as well as quotes, commas, and semi-colons as shown below.
{"yaxis": 128, "xaxis": 120, "col": [111, 108, 109, 110, 108, 101], "val": [114.35938, 89.78516, 67.86719, 36.28906, 62.14258, 55.28305], "row": [43, 52, 52, 52, 53, 54]}
The yaxis and xaxis fields always have 1 value, but the col, val, and row fields can have anywhere from 0 to hundreds of values (here there are 6 separate values). The col, val, and row fields are related and should always contain the same quantity of values.
What I would want is something like (yaxis and xaxis could be filled in with 128 and 120 for each cell):
I have tried using dlmstr = ', "'; and delimstr = ': '; to little success. Any guidance would be appreciated. Thanks!
data have;
infile cards dlm='{},[] :';
input x : $40. @@;
x=dequote(x);
cards;
{"yaxis": 128, "xaxis": 120, "col": [111, 108, 109, 110, 108, 101], "val": [114.35938, 89.78516, 67.86719, 36.28906, 62.14258, 55.28305], "row": [43, 52, 52, 52, 53, 54]}
;
run;
data temp;
set have;
if anyalpha(x) then group+1;
run;
proc transpose data=temp out=temp1(drop=_name_ group rename=(col1=id));
by group;
var x;
run;
proc transpose data=temp1 out=want;
var col:;
id id;
run;
If you are using SAS 9.4 maintenance 4 or better, you can use the JSON LIBNAME engine to read in your line of JSON. Here is the code I threw together that produces your results. Note that MAP='MY.MAP' in the LIBNAME statement is just a file name for the JSON LIBNAME engine to place the generated JSON map file.
libname x json 'sasuser\jsonInfile1.txt' map='my.map' automap=reuse;
data work.xaxis(keep=value rename=(value=xaxis))
work.yaxis(keep=value rename=(value=yaxis))
work.col (keep=value rename=(value=col))
work.row (keep=value rename=(value=row))
work.val (keep=value rename=(value=val));
set x.alldata;
if (V = 1)
then do;
if (p2 EQ "")
then do;
select (p1);
when ("yaxis") output work.yaxis;
when ("xaxis") output work.xaxis;
otherwise put "Unexpected :" p1;
end;
end;
else do;
select (substr(p2,1,3));
when ("col") output work.col;
when ("val") output work.val;
when ("row") output work.row;
otherwise put "Unexpected :" p2;
end;
end;
end;
run;
data work.merged;
merge work.xaxis work.yaxis work.col work.row work.val;
run;
proc print data=work.merged; run;
Thanks for the help all!
Learned a lot of new syntax along the way 🙂
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: