topic Re: Infile for one long line of data in SAS Programming

Infile for one long line of data

how2infile — Mon, 12 Aug 2019 19:59:42 GMT

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!

Re: Infile for one long line of data

Reeza — Mon, 12 Aug 2019 20:54:18 GMT

That looks like XML or JSON. If so, you can try either the XML or JSON libnames to read in the data instead of INFILE.

Re: Infile for one long line of data

Ksharp — Tue, 13 Aug 2019 12:05:55 GMT

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;

Re: Infile for one long line of data

BillM_SAS — Tue, 13 Aug 2019 17:21:40 GMT

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;

Re: Infile for one long line of data

how2infile — Wed, 14 Aug 2019 14:48:39 GMT

Thanks for the help all!

Learned a lot of new syntax along the way 🙂