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

drchiragvyas — Wed, 24 May 2023 23:08:23 GMT

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:

Device_width (corresponds to response of "screenW":)
Device_height (corresponds to response of "screenH":)
Touch (corresponds to response of "touch":)
Device_OS (corresponds to response of "OS":)

Thank you!

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

Tom — Thu, 25 May 2023 03:50:33 GMT

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;

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

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

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