Yes, it is definitely full of quirks in 9.4M2, I for one and looking forward to seeing it improve.
FriedEgg, Many thanks for the great example.
Bruno
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.
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 .
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
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!
Here is an example about how to import JSON.
I can not use proc groovy I can't install the required jar files.
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...
However, PROC GROOVY requires XCMD privileges. The default of NOXCMD in a typical SAS server configuration would prevent PROC GROOVY from working.
Chris
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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.