topic Re: Parse json file with Proc Groovy in SAS Procedures

Parse json file with Proc Groovy

BillJones — Sun, 01 Mar 2015 07:08:21 GMT

Hello everyone,

I'm trying to parse a json file with proc groovy. I believe that I correctly referenced the necessary jar files. However, I'm still getting errors. I'm running SAS 9.3 on Windows 7 Professional. Json file, code, and log are below. Any help or suggestions would be greatly appreciated.

Thanks very much,

Bill

json file

{"results":

[

{

"acct_nbr": 1234,

"firstName": "John",

"lastName": "Smith",

"age": 25,

"address": {

"streetAddress": "21 2nd Street",

"city": "New York",

"state": "NY",

"postalCode": "10021"

}

{

"acct_nbr": 3456,

"firstName": "Sam",

"lastName": "Jones",

"age": 32,

"address": {

"streetAddress": "25 2nd Street",

"city": "New Jersy",

"state": "NJ",

"postalCode": "10081"

}

]

}

Code:

dm 'clear log';

%let sourcefile=C:\json.txt;

%let outfile=c:\json.csv;

proc groovy;

add classpath="C:\Program Files (x86)\Groovy\Groovy-2.4.0\lib\groovy-all.2.4.0.jar";

add classpath="C:\Program Files (x86)\Groovy\Groovy-2.4.0\lib\opencsv-3.2.jar";

submit "&sourcefile" "&outfile";

import groovy.json.*

import au.com.bytecode.opencsv.CSVWriter

def input = new File(args[0]).text

def output = new JsonSlurper().parseText(input)

def csvoutput = new FileWriter(args[1])

CSVWriter writer = new CSVWriter(csvoutput);

String[] header = new String[8];

header[0] = "results.acct_nbr";

header[1] = "results.firstName";

header[2] = "results.lastName";

header[3] = "results.age";

header[4] = "results.address.streetAddress";

header[5] = "results.address.city";

header[6] = "results.address.state";

header[7] = "results.address.postalCode";

writer.writeNext(header);

output.statuses.each {

String[] content = new String[8];

content[0] = it.results.acct_nbr.toString();

content[1] = it.results.firstName.toString();

content[2] = it.results.lastName.toString();

content[3] = it.results.age.toString();

content[4] = it.results.address.streetAddress.toString();

content[5] = it.results.address.city.toString();

content[6] = it.results.address.state.toString();

content[7] = it.results.address.postalCode.toString();

writer.writeNext(content)

}

writer.close();

endsubmit;

quit;

Log:

NOTE: The ADD CLASSPATH command completed.

ERROR: The SUBMIT command failed.

org.codehaus.groovy.control.MultipleCompilationErrorsException:

startup failed:

Script58.groovy: 2: unable to resolve class

au.com.bytecode.opencsv.CSVWriter

@ line 2, column 7.

import au.com.bytecode.opencsv.CSVWriter

Script58.groovy: 5: unable to resolve class JsonSlurper

@ line 5,

column 20.

def output = new JsonSlurper().parseText(input)

2 errors

org.codehaus.groovy.control.ErrorCollector.failIfErrors(ErrorCollector

.java:296)

org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(Compila

tionUnit.java:860)

org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(Compilati

onUnit.java:521)

org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(Com

pilationUnit.java:497)

org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.ja

va:474)

groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:292)

at groovy.lang.GroovyShell.parseClass(GroovyShell.java:727)

at groovy.lang.GroovyShell.parse(GroovyShell.java:739)

at groovy.lang.GroovyShell.parse(GroovyShell.java:766)

at groovy.lang.GroovyShell.parse(GroovyShell.java:757)

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE GROOVY used (Total process time):

real time 0.01 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 34.53k

OS Memory 25028.00k

Timestamp 03/01/2015 01:19:49 AM

Re: Parse json file with Proc Groovy

FriedEgg — Sun, 01 Mar 2015 16:10:14 GMT

filename cp temp;

proc groovy classpath=cp;

add sasjar="groovy_2.1.3" version="2.1.3.0_SAS_20130517000930";

submit parseonly;

import groovy.json.JsonSlurper

class MyJsonParser {

def parseFile(path) {

def jsonFile = new File(path)

def jsonText = jsonFile.getText()

def InputJSON = new JsonSlurper().parseText(jsonText)

def accounts = []

InputJSON.results.each{

accounts << [

acct_nbr : it.acct_nbr.toString(),

firstName : it.firstName,

lastName : it.lastName,

age : it.age.toString(),

streetAddress : it.address.streetAddress,

city : it.address.city,

state : it.address.state,

postalCode : it.address.postalCode

]

}

return accounts

}

endsubmit;

submit parseonly;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.LinkedHashMap;

public class MyJsonParser4Sas {

public String filename = "";

public void init() {

MyJsonParser myParser = new MyJsonParser();

accounts = myParser.parseFile(filename);

iter = accounts.iterator();

}

public boolean hasNext() {

return iter.hasNext();

}

public void getNext() {

account = ((LinkedHashMap) (iter.next()));

}

public String getString(String k) {

return account.get(k);

}

protected ArrayList accounts;

protected Iterator iter;

protected LinkedHashMap account;

}

endsubmit;

quit;

options set=classpath "%sysfunc(pathname(cp,f))";

filename json "/home/mkastin/Desktop/foo.json";

data accounts;

attrib id label="Account Index" length= 8

acct_nbr label="Account Number" length=$ 10

firstName label="First Name" length=$ 20

lastName label="Last Name" length=$ 30

age label="Age" length=$ 3

streetAddress label="Street Address" length=$ 128

city label="City" length=$ 40

state label="State" length=$ 2

postalCode label="Postal Code" length=$ 5;

dcl javaobj accounts("MyJsonParser4Sas");

accounts.exceptiondescribe(1);

accounts.setStringField("filename", "%sysfunc(pathname(json,f))");

accounts.callVoidMethod("init");

accounts.callBooleanMethod("hasNext",rc);

do id=1 by 1 while(rc);

accounts.callVoidMethod("getNext");

accounts.callStringMethod("getString", "acct_nbr", acct_nbr);

accounts.callStringMethod("getString", "firstName", firstName);

accounts.callStringMethod("getString", "lastName", lastName);

accounts.callStringMethod("getString", "age", age);

accounts.callStringMethod("getString", "streetAddress", streetAddress);

accounts.callStringMethod("getString", "city", city);

accounts.callStringMethod("getString", "state", state);

accounts.callStringMethod("getString", "postalCode", postalCode);

output;

accounts.callBooleanMethod("hasNext",rc);

end;

drop rc;

run;

1	1234	John	Smith	25	21 2nd Street	New York	NY	10021
2	3456	Sam	Jones	32	25 2nd Street	New Jersy	NJ	10081

A few notes:

1. The json as shared here is invalid and should be wrapped in "{}"

2. In windows you will need to modify the file's name in one of following ways

2a. escape the \ as \\, for example C:\json.txt would be C:\\json.txt

2b. use forward slash instead of backslash, for example C:\json.txt would be /C:/json.txt

2c. use a file uri specification ie. file://C:/json.txt

3. The add sasjar statement is specific to your version or SAS and OS, check your versioned jar repositiory as part of your installation or use a different groovy-all jar, as you had in OP

Re: Parse json file with Proc Groovy

BillJones — Mon, 02 Mar 2015 03:00:05 GMT

Friedegg,

Thanks so much for your code and thoughts on this problem. I made your suggested changes, but still cannot get the code to run. Note I tried using both the groovy-all jar that was installed with SAS and a newer version. I got the same error in both cases.

-Bill

Log:

1661 dm 'clear log';

1662

1663 options mprint;

1664

1665 filename cp temp;

1666

1667 proc

1667! groovy classpath=cp;

NOTE: The ADD CLASSPATH command completed.

1668

1669 add classpath="C:\Program Files (x86)\Groovy\Groovy-2.4.0\lib\groovy-all.2.4.0.jar";

NOTE: The ADD CLASSPATH command completed.

1670

1671 submit parseonly;

1672 import groovy.json.JsonSlurper

1673 class MyJsonParser {

1674 def parseFile(path) {

1675 def jsonFile = new File(path)

1676 def jsonText = jsonFile.getText()

1677 def InputJSON = new JsonSlurper().parseText(jsonText)

1678 def accounts = []

1679

1680

1681 InputJSON.results.each{

1682 accounts << [

1683 acct_nbr : it.acct_nbr.toString(),

1684 firstName : it.firstName,

1685 lastName : it.lastName,

1686 age : it.age.toString(),

1687 streetAddress : it.address.streetAddress,

1688 city : it.address.city,

1689 state : it.address.state,

1690 postalCode : it.address.postalCode

1691 ]

1692 }

1693

1694

1695 return accounts

1696 }

1697 }

1698 endsubmit;

ERROR: The SUBMIT command failed.

org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:

script14252651249521306453411.groovy: 1: unable to resolve class groovy.json.JsonSlurper

@ line 1,

column 1.

import groovy.json.JsonSlurper

1 error

at org.codehaus.groovy.control.ErrorCollector.failIfErrors(ErrorCollector.java:296)

at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:860)

at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:521)

at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:497)

at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:474)

at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:292)

at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:263)

at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:207)

at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:217)

1699

1700

1701 submit parseonly;

1702 import java.util.ArrayList;

1703 import java.util.Iterator;

1704 import java.util.LinkedHashMap;

1705

1706

1707 public class MyJsonParser4Sas {

1708 public String filename = "";

1709

1710

1711 public void init() {

1712 MyJsonParser myParser = new MyJsonParser();

1713 accounts = myParser.parseFile(filename);

1714 iter = accounts.iterator();

1715 }

1716

1717

1718 public boolean hasNext() {

1719 return iter.hasNext();

1720 }

1721

1722

1723 public void getNext() {

1724 account = ((LinkedHashMap) (iter.next()));

1725 }

1726

1727

1728 public String getString(String k) {

1729 return account.get(k);

1730 }

1731

1732

1733 protected ArrayList accounts;

1734 protected Iterator iter;

1735 protected LinkedHashMap account;

1736 }

1737 endsubmit;

ERROR: The SUBMIT command failed.

org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:

script1425265124971621978172.groovy: 11: unable to resolve class MyJsonParser

@ line 11, column 22.

MyJsonParser myParser = new MyJsonParser();

script1425265124971621978172.groovy: 11: unable to resolve class MyJsonParser

@ line 11, column 33.