Help using Base SAS procedures

Parse json file with Proc Groovy

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

Parse json file with Proc Groovy

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.

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

     at

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

.java:296)

     at

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

tionUnit.java:860)

     at

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

onUnit.java:521)

     at

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

pilationUnit.java:497)

     at

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

va:474)

     at

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


Accepted Solutions
Solution
‎03-02-2015 02:58 PM
Trusted Advisor
Posts: 1,301

Re: Parse json file with Proc Groovy

Posted in reply to BillJones

The simplest possible explanation is that you are not setting up your classpath correctly.  You should validate that your path to the groovy-all jar is correct.  In my version groovy-all is in the embeddable subdirectory, not the lib.  If the path is correct, you should validate the contents of groovy-all jar as it may be missing the groovy.json package.  The following works for me on the Windows machine with SAS 9.3

filename cp temp;

proc groovy classpath=cp;

add classpath="C:\Program Files\Java\groovy-2.3.4\embeddable\groovy-all-2.3.4.jar";

/*or*/

/*

add classpath="C:\Program Files\Java\groovy-2.3.4\lib\groovy-2.3.4.jar";

add classpath="C:\Program Files\Java\groovy-2.3.4\lib\groovy-json-2.3.4.jar";

*/

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))";

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", "C:\\foo.json");

   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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,301

Re: Parse json file with Proc Groovy

Posted in reply to BillJones

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;

         

11234JohnSmith2521 2nd StreetNew YorkNY10021
23456SamJones3225 2nd StreetNew JersyNJ10081

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

Frequent Contributor
Posts: 92

Re: Parse json file with Proc Groovy

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.

           MyJsonParser myParser = new MyJsonParser();

                                   ^

2 errors

     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)

1738

1739

1740  quit;

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

NOTE: PROCEDURE GROOVY used (Total process time):

      real time           0.04 seconds

      cpu time            0.01 seconds

1741

1742

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

1744  filename json "/C:/json.txt";

1745

1746

1747  data accounts;

1748     attrib id            label="Account Index"  length=    8

1749            acct_nbr      label="Account Number" length=$  10

1750            firstName     label="First Name"     length=$  20

1751            lastName      label="Last Name"      length=$  30

1752            age           label="Age"            length=$   3

1753            streetAddress label="Street Address" length=$ 128

1754            city          label="City"           length=$  40

1755            state         label="State"          length=$   2

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

1757

1758

1759     dcl javaobj accounts("MyJsonParser4Sas");

1760     accounts.exceptiondescribe(1);

1761

1762

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

1764

1765

1766     accounts.callVoidMethod("init");

1767

1768

1769     accounts.callBooleanMethod("hasNext",rc);

1770     do id=1 by 1 while(rc);

1771        accounts.callVoidMethod("getNext");

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

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

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

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

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

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

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

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

1780        output;

1781     accounts.callBooleanMethod("hasNext",rc);

1782     end;

1783

1784     drop rc;

1785  run;

ERROR: Could not find class MyJsonParser4Sas at line 1759 column 24.  Please ensure that the

       CLASSPATH is correct.

ERROR: DATA STEP Component Object failure.  Aborted during the EXECUTION phase.

java.lang.ClassNotFoundException: MyJsonParser4Sas

     at java.net.URLClassLoader$1.run(Unknown Source)

     at java.security.AccessController.doPrivileged(Native Method)

     at java.net.URLClassLoader.findClass(Unknown Source)

     at java.lang.ClassLoader.loadClass(Unknown Source)

     at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:674)

     at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:773)

     at java.lang.ClassLoader.loadClass(Unknown Source)

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

WARNING: The data set WORK.ACCOUNTS may be incomplete.  When this step was stopped there were 0

         observations and 9 variables.

WARNING: Data set WORK.ACCOUNTS was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

Solution
‎03-02-2015 02:58 PM
Trusted Advisor
Posts: 1,301

Re: Parse json file with Proc Groovy

Posted in reply to BillJones

The simplest possible explanation is that you are not setting up your classpath correctly.  You should validate that your path to the groovy-all jar is correct.  In my version groovy-all is in the embeddable subdirectory, not the lib.  If the path is correct, you should validate the contents of groovy-all jar as it may be missing the groovy.json package.  The following works for me on the Windows machine with SAS 9.3

filename cp temp;

proc groovy classpath=cp;

add classpath="C:\Program Files\Java\groovy-2.3.4\embeddable\groovy-all-2.3.4.jar";

/*or*/

/*

add classpath="C:\Program Files\Java\groovy-2.3.4\lib\groovy-2.3.4.jar";

add classpath="C:\Program Files\Java\groovy-2.3.4\lib\groovy-json-2.3.4.jar";

*/

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))";

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", "C:\\foo.json");

   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;

Frequent Contributor
Posts: 92

Re: Parse json file with Proc Groovy

FriedEgg,

It was the classpath.  I added groovy-2.4.0.jar and groovy-json-2.4.0.jar separately, and the code runs!  Thanks so much for your assistance in this matter. 

Note the json parser seems to execute quickly.  Now that you've given me a working  template, I'll try to compare json with xml to see which is faster.

Thanks again.

Bill

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 3800 views
  • 5 likes
  • 2 in conversation