BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
FriedEgg
SAS Employee

Yes, it is definitely full of quirks in 9.4M2, I for one and looking forward to seeing it improve.

BrunoMueller
SAS Super FREQ

FriedEgg, Many thanks for the great example.

Bruno

anu_ha
Calcite | Level 5

Thanks for the Lua example, unfortunately I don't have 9.4 and can not use groovy with restriction around installation. Let me know if anyone has suggestion around the lost card that might solve the problem.

Ksharp
Super User

First of all, Your JSON is not right , you missed ']}' at the end of JSON.

And the most simple way is using online parse website to parse JSON . Like :  http://json.cn/

And the following is solution by SAS data step . Hope this could help you .

Code: Program


data have;
infile '/folders/myfolders/have.json' recfm=n dlm='{}[],';
input value : $200. @@;
if value in: ('"count"' '"calls"' '"additionalParameters"') then delete;
run;


data temp;
set have;
length name v $ 100;
if value =: '"connectedTo"' then n+1;
name=scan(value,2,'"','m');
v=scan(value,-2,'"','m');
drop value;
run;
proc transpose data=temp out=want(drop=_name_ n) ;
by n;
var v;
id name;
run;

Xia Keshan

anu_ha
Calcite | Level 5

I had given those records as sample so the file was not complete.

Thanks for your reply and the solution. It works with my file so really appreciate it!

anu_ha
Calcite | Level 5

I can not use proc groovy I can't install the required jar files.

FriedEgg
SAS Employee

In newer versions of SAS the required jar files are typically included in the installation's versioned jar repository, but not necessarily used as the default.  For instance, I have a Windows installation 9.4TS1M2 and it uses groovy-all.jar of version ~1.7 that does not included the groovy.json package.  However, my installation has a package of groovy-all version ~2.1 which does include it.  I can then use this newer version by doing the following:

proc groovy;

add sasjar="groovy_2.1.3" version="2.1.3.0_SAS_20130517000930"; /*this exact version can different from product-to-product and by OS, etc...*/

submit;

import groovy.json.JsonSlurper

def slurper = new JsonSlurper()

def result = slurper.parseText('{"person":{"name":"Guillaume", "age":33, "pets":["dog", "cat"]}}')

assert result.person.name == "Guillaume"

assert result.person.age == 33

assert result.person.pets.size() == 2

assert result.person.pets[0] == "dog"

assert result.person.pets[1] == "cat"

endsubmit;

quit;

So long as you have internet access you can also download any necessary JAR to the work directory for use in your process...

ChrisHemedinger
Community Manager

However, PROC GROOVY requires XCMD privileges.  The default of NOXCMD in a typical SAS server configuration would prevent PROC GROOVY from working.

Chris

SAS For Dummies 3rd Edition! Check out the new edition, covering SAS 9.4, SAS Viya, and all of the modern ways to use SAS!
FriedEgg
SAS Employee

This is indeed true, however, by the OP's comment, he can execute PROC GROOVY, but believe he is unable to 'install' the necessary JAR files.  A JSON package for DS2 is very exciting and something I've been looking forward to since it was pre-production.  Wondering now how it will play with the HTTP package and streaming content.  Parsing a JSONstring would also be possible with PROC LUA (another very exciting new/shiny thing), but with no OS access from that procedure (at this time) you would not be able to read from a text file directly, however, with effort, you could go directly from JSON string to SAS Data file.

ChrisHemedinger
Community Manager

For completeness, I'm updating this post with the latest info: a JSON libname engine is now in Base SAS 9.4 (maint 4).

 

Example:

 

filename resp temp;
 
/* Neat service from Open Notify project */
proc http 
 url="http://api.open-notify.org/astros.json"
 method= "GET"
 out=resp;
run;
 
/* Assign a JSON library to the HTTP response */
libname space JSON fileref=resp;
 
/* Print result, dropping automatic ordinal metadata */
title "Who is in space right now? (as of &sysdate)";
proc print data=space.people (drop=ordinal:);
run;

 

SAS For Dummies 3rd Edition! Check out the new edition, covering SAS 9.4, SAS Viya, and all of the modern ways to use SAS!
AhmedAl_Attar
Ammonite | Level 13

Check this SAS Global Forum paper http://www.lexjansen.com/wuss/2013/103_Paper.pdf.

It has SAS code that you can run within SAS EG to read Twitter JSON Feed.

Hope this helps,

Ahmed

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 26 replies
  • 15570 views
  • 5 likes
  • 8 in conversation