How to create multiple variables from a variable with a long raw in SAS

[drchiragvyas]

One variable (device_scan) in my dataset is very long. I would like to create multiple variables using responses in this long variable.

See below example,

Obs            device_scan

       1        {"screenW":1098,"screenH":618,"touch":true,"OS":"Windows","UTCoffset":300}

       2       {"screenW":1024,"screenH":768,"touch":true,"OS":"MacOS","UTCoffset":240}

       3       {"screenW":1536,"screenH":864,"touch":true,"OS":"Windows","UTCoffset":300}

       4       {"screenW":1280,"screenH":1024,"touch":false,"OS":"Windows","UTCoffset":240}

       5       {"screenW":1080,"screenH":810,"touch":true,"OS":"MacOS","UTCoffset":300}

I would like to create 4 variables using the "device_scan" row:

1. Device_width (corresponds to response of "screenW":)
2. Device_height (corresponds to response of "screenH":)
3. Touch (corresponds to response of "touch":)
4. Device_OS (corresponds to response of "OS":)

Thank you!

[Tom]

Looks like JSON.

It is probably going to be easiest to write the strings to a file and use the JSON libref engine to convert the JSON into dataset(s).

So if you have this dataset:

data have ;
  input device_scan $80.;
cards;
{"screenW":1098,"screenH":618,"touch":true,"OS":"Windows","UTCoffset":300}
{"screenW":1024,"screenH":768,"touch":true,"OS":"MacOS","UTCoffset":240}
{"screenW":1536,"screenH":864,"touch":true,"OS":"Windows","UTCoffset":300}
{"screenW":1280,"screenH":1024,"touch":false,"OS":"Windows","UTCoffset":240}
{"screenW":1080,"screenH":810,"touch":true,"OS":"MacOS","UTCoffset":300}
;

You can use a data step like this to write an actual JSON file from those strings.

filename json temp;
data _null_;
  set have end=eof;
  file json;
  if _n_=1 then put '[' @;
  else put ',' @;
  put device_scan ;
  if eof then put ']';
run;

Then you can use the JSON libref engine to read it.

libname json json ;
proc print data=json.root;
run;

Result

And if instead of having the JSON strings in a dataset you actually already have them in a file with one JSON string per line then what you have is called a JSONL (JSON Lines) file.  (There is another acronym people use for that type of file, but it is harder to remember than JSON LINES).

{"screenW":1098,"screenH":618,"touch":true,"OS":"Windows","UTCoffset":300}
{"screenW":1024,"screenH":768,"touch":true,"OS":"MacOS","UTCoffset":240}
{"screenW":1536,"screenH":864,"touch":true,"OS":"Windows","UTCoffset":300}
{"screenW":1280,"screenH":1024,"touch":false,"OS":"Windows","UTCoffset":240}
{"screenW":1080,"screenH":810,"touch":true,"OS":"MacOS","UTCoffset":300}

Then you still need to add the [] and commas to make it back into an actual JSON file.

filename json temp;
data _null_;
  infile 'myfile.jsonl' end=eof;
  file json;
  if _n_=1 then put '[' @;
  else put ',' @;
  input;
  put _infile_;
  if eof then put ']';
run;