DATA Step, Macro, Functions and more

reading in json

Posts: 21

reading in json

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. @@;
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");
proc print data=want;



                 Obs    x             value

                   1    id            89702492
                   2    time          2016-10-06 14:26:25 UTC
                   3    ip_address
                   4    identity      xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
                   5    page
                   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
                  16    identity      xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
                  17    page
                  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. 



Super User
Posts: 24,027

Re: reading in json

See the methods illustrated here. 

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

Super User
Posts: 8,220

Re: reading in json

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

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

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

Art, CEO,

Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation