reading in json

rboire — Thu, 25 May 2017 22:58:51 GMT

Given the output below after running some code in manipulating data from JSON.

data x;
infile '\\kashmir\BFD\sample.json.txt' lrecl=320000 dsd dlm='{},:[]';
input x : $400. @@;
run;
data want;
merge x x(firstobs=2 rename=(x=value));
if lowcase(x) in ("id" "time" "ip_address" "identity" "page"
"user_agent" "answer" "comment" "sessionid"
"language" "locale" "channel");
run;
proc print data=want;
run;

Output:

Obs x value

                   1    id            89702492
                   2    time          2016-10-06 14:26:25 UTC
                   3    ip_address    111.111.111.111
                   4    identity      xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
                   5    page          https://secure.hello.com
                   6    user_agent    Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit
                   7    answer        Other please say
                   8    comment       I dont know
                   9    SessionID     4444444444
                  10    Language      fr
                  11    Locale        Be
                  12    Channel       Affiliate
                  13    id            89702444
                  14    time          2014-10-14 14:26:25 UTC
                  15    ip_address    111.111.111.111
                  16    identity      xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
                  17    page          https://secure.goodbye.com
                  18    user_agent    Mozilla/5.0 (Windows NT 6.3; WOW64) ricjie
                  19    answer        Other please say
                  20    comment       I dont know
                  21    SessionID     3124444444
                  22    Language      fr
                  23    Locale        Be
                  24    Channel       nothing

How do I convert above data such that a new record begins with the key ID and all the subsequent keys along with ID are variables.

So in above case, have two records with variables beginning with ID and ending in channel.

Re: reading in json

Reeza — Fri, 26 May 2017 00:00:26 GMT

See the methods illustrated here.

How you read JSON is dependent on your SAS version, obviously the later versions are better.

http://blogs.sas.com/content/sasdummy/2015/09/28/parse-json-from-sas/

Re: reading in json

art297 — Fri, 26 May 2017 00:06:07 GMT

I think you only need to transpose what you have. e.g.:

data want;
  set want;
  if x='id' then counter+1;
run;

proc transpose data=want out=want(drop=_: counter);
  by counter;
  var value;
  id x;
run;

Art, CEO, AnalystFinder.com

topic reading in json in SAS Programming

reading in json

Re: reading in json

Re: reading in json