FriedEgg Tracker

Re: Allow PROC SORT to output multiple datasets

FriedEgg — Fri, 04 Jun 2021 16:11:53 GMT

With this following syntax, which looks almost identical to that shown by @yabwon you can accomplish the goal here, which is to avoid the extra IO from reading the sorted file to subsequently split into multiple datasets. This uses the experimental feature called "Output Data Step Views" which are incredibly powerful!

data cars_asia(where=(origin='Asia'))
     cars_europe(where=(origin='Europe'))
     cars_usa(where=(origin='USA')) 
     / 
     view=split_sort;

  if 0 then set sashelp.cars;
  set split_sort;
run;

proc sort data=sashelp.cars out=split_sort; 
  by msrp;
run;

1   data cars_asia(where=(origin='Asia'))
2        cars_europe(where=(origin='Europe'))
3        cars_usa(where=(origin='USA'))
4        /
5        view=split_sort;
6   
7     if 0 then set sashelp.cars;
8     set split_sort;
9   run;
NOTE: DATA STEP view saved on file WORK.SPLIT_SORT.
NOTE: A stored DATA STEP view cannot run under a different operating system.
WARNING: The definition of an output DATA step view is an experimental feature in this release and is not intended for use in the 
         development of production applications.
      
10   
11   proc sort data=sashelp.cars out=split_sort;
12     by msrp;
13   run;
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set WORK.SPLIT_SORT has 428 observations and 15 variables.     
NOTE: The data set WORK.CARS_ASIA has 158 observations and 15 variables.
NOTE: The data set WORK.CARS_EUROPE has 123 observations and 15 variables.
NOTE: The data set WORK.CARS_USA has 147 observations and 15 variables.

Re: Help converting a CURL command into PROC HTTP

FriedEgg — Tue, 19 Jan 2021 22:58:25 GMT

Steve,

Stepping through your attempts, you are close each time, but have simple mistakes we can easily address:

Attempt 1

When you use the IN= option with a quoted string, that data is posted, literally. So instead of sending the contents of the JSON file as you probably assume you are, you are actually just sending the web service the literal string "S:\My School QA\2021 Data Collection\SBD_Independents\FTP_Account_Creation\Requests\test1.json". The web service is expecting JSON data (because you specified the content_type), which this is not, so it responds with "Bad Request"

Instead you want to use the fileref you created when you wrote the json data to a file in the first data step ie:

filename json_in temp;
...
proc http
...
    in=json_in /*no quotes*/
...

Attempt 2

Here you have a similar issue. You are using the FORM method on the IN= option. This helper method serializes the data you provide as a content-type such as application/x-www-form-urlencoded which your web service doesn't appear to support. The bad content type and data is why you receive the "Unprocessable Entity" error

You cannot use the FORM or MULTI helpers here in the IN= option as they are not valid for the type of data you want to POST

Attempt 3

This time the issue is slightly different. You are correctly trying to use the IN= option with string data equivalent to the JSON you want to post, however, you are using single quotes inside your JSON which is invalid syntax. You also should be including the CT= option with application/json

Instead, you want to use double quotes inside the JSON string as you have elsewhere in your post here:

proc http
...
in="{""notify"": true, ""emails"": [""croftie@tpg.co.au""], ""roleId"": 7, ""notifyFileAdded"": false}"
...

Attempt 4

This is essentially the same as attempt 1

Attempt 5

This is essentially the same as attempt 3, this is invalid JSON syntax so the web service cannot handle the data it receives.

Additional Information

Across all of your attempts you are also leaving out additional headers that could be important to your web service successfully responding to your request. Here is the most complete translation of your curl command in the HTTP Procedure

proc http
    method="POST"
    url="https://pluto.acara.edu.au/rest/folders/100/members"
    ct="application/json"
    oauth_bearer="&token" /* equivalent of headers "Authorization"="Bearer &token" */
    in="{""notify"": true, ""emails"": [""croftie@tpg.co.au""], ""roleId"": 7, ""notifyFileAdded"": false}";
    headers 
        "Accept"="application/json"
        "X-Accellion-Version"="16";
    debug level=3 NO_REQUEST_BODY NO_REQUEST_HEADERS OUTPUT_TEXT;
run;

Re: EG 8.1 and Studio Integration for tasks - custom tasks?

FriedEgg — Wed, 04 Mar 2020 16:40:37 GMT

@ChrisHemedinger and @CaseySmith how is this looking on the roadmap. I'm still waiting!

Re: Translating cURL command to equivalent PROC HTTP statement?

FriedEgg — Thu, 09 Jan 2020 16:46:57 GMT

-u in cURL refers to user name/password for basic authentication. In the below, "sas.cli" would be the username and whatever is after the ":" would be the password.

In PROC HTTP you are using AUTH_NTLM instead of basic authentication. You would instead want to use AUTH_BASIC and WEB_USERNAME and WEB_PASSWORD options.

Re: Getting SAS script to run run from Python using SASPy

FriedEgg — Tue, 29 Oct 2019 15:07:33 GMT

See the SASPy API Reference

https://sassoftware.github.io/saspy/api.html#saspy.SASsession.submit

Using %include as Tom suggests would presume that Jupyter, where you said you uploaded the code, is the same machine where you are submitting to SAS.

Two additional options you have:

1. Ditch the .sas file and put it's contents directly into your notebook as such:

results_dict = sas.submit(
             """
             libname tera teradata server=teracop1 user=user pw=pw;
             proc print data=tera.dsname (obs=10); run;
             """
             )

2. Read the file contents into Python and then submit:

code = open('/users/myuserid.files/SAS_filename.sas').read()
results_dict = sas.submit(code)

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Tue, 24 Sep 2019 22:35:15 GMT

I updated the example on GitHub to include an insert. Good luck.

https://github.com/FriedEgg/SAS-JDBC-Spring-Boot-Example

Re: Trying to get the output data of my sas program in java

FriedEgg — Tue, 24 Sep 2019 15:02:44 GMT

@SergioSAS,

Below is an example of using the IDataSet interface, however, you may find it simpler to use JDBC instead.

import com.sas.iom.SAS.*;
import com.sas.iom.SASIOMDefs.*;
import com.sas.services.connection.*;
import org.omg.CORBA.IntHolder;
import org.omg.CORBA.StringHolder;

import java.util.Arrays;

public class SasOutputDataExample {
    public static void main(String... args) {
        ConnectionInterface cx = null;
        boolean failed = false;

        try {
            BridgeServer server = new BridgeServer(BridgeServer.CLSID_SAS, "sas.myhost.net", 8591);
            server.setEncryptionContent(BridgeServer.ENCRYPTION_CONTENT_ALL);
            server.setEncryptionAlgorithms(BridgeServer.ENCRYPTION_ALGORITHM_AES);
            server.setSecurityPackage(BridgeServer.SECURITY_PACKAGE_NEGOTIATE);
            server.setSecurityPackageList(BridgeServer.SECURITY_PACKAGE_LIST_DEFAULT);
            ManualConnectionFactoryConfiguration mconf = new ManualConnectionFactoryConfiguration(server);
            ConnectionFactoryManager manager = new ConnectionFactoryManager();
            ConnectionFactoryInterface factory = manager.getFactory(mconf);
            SecurityPackageCredential cred = new SecurityPackageCredential();
            cx = factory.getConnection(cred);
        } catch (ConnectionFactoryException err) {
            System.out.print(err.getMessage());
            failed = true;
        }

        if (!failed) {
            IWorkspace workspace = IWorkspaceHelper.narrow(cx.getObject());

            try {
                ILanguageService ls = workspace.LanguageService();
                ls.Submit("data kids; set sashelp.class; run;");
                ILibref libWork = workspace.DataService().UseLibref("work");
                int flags = 0;
                StringHolder dataSetLabel = new StringHolder();
                StringHolder dataSetType = new StringHolder();
                DateTimeHolder dateCreated = new DateTimeHolder();
                DateTimeHolder dateModified = new DateTimeHolder();
                IntHolder recordLength = new IntHolder();
                StringHolder compressionRoutine = new StringHolder();
                IntHolder bookmarkLength = new IntHolder();
                IntHolder logicalRecordCount = new IntHolder();
                IntHolder physicalRecordCount = new IntHolder();
                IntHolder attrs = new IntHolder();
                IDataSet kids = libWork.OpenDataSet(flags, "kids", "", new String[]{"", "", ""}, dataSetLabel, dataSetType, dateCreated, dateModified, recordLength, compressionRoutine, bookmarkLength, logicalRecordCount, physicalRecordCount, attrs);
                int bindKey = 0;
                byte[] positionBookmark = new byte[]{};
                int numberRowsToRead = 2;
                int rowsOffset = 0;
                VariableArray2dOfStringHolder characterValues = new VariableArray2dOfStringHolder();
                VariableArray2dOfDoubleHolder numericValues = new VariableArray2dOfDoubleHolder();
                VariableArray2dOfOctetHolder missingValues = new VariableArray2dOfOctetHolder();
                OctetSeqHolder bookmarks = new OctetSeqHolder();
                IntHolder status = new IntHolder();
                kids.ReadRecords(flags, bindKey, positionBookmark, numberRowsToRead, rowsOffset, characterValues, numericValues, missingValues, bookmarks, status);
                for (int i = 0; i < numberRowsToRead; i++) {
                    System.out.println(Arrays.toString(characterValues.value[i])); //character values are formatted
                }
                workspace.Close();
                cx.close();
            } catch (Exception err) {
                err.printStackTrace();
            }
        }
    }
}

This should then print the following to the console

[Alfred  , M, Alfred  , M,           14,           69,        112.5]
[Alice   , F, Alice   , F,           13,         56.5,           84]

IDataSet can also provide you with the metadata for the data set (i.e. Column Names, etc...)

Re: SAS Advertising api java coding

FriedEgg — Mon, 02 Sep 2019 13:59:31 GMT

Since you did not really qualify if you are interested in SAS Viya or SAS MVA and all the resources linked here are solely for Viya I will include the references for the latter.

SAS® 9.4 Integration Technologies: Java Client Developer’s Guide
https://documentation.sas.com/?docsetId=itechjcdg&docsetTarget=titlepage.htm&docsetVersion=9.4&locale=en

SAS 9.4 BI API Documentation
https://support.sas.com/rnd/javadoc/94/index.html

Re: How Do I Access SAS Data Set with JDBC?

FriedEgg — Fri, 12 Jul 2019 15:09:50 GMT

You may find this other post here helpful, and I do list there the minimum set of jar files to use the MVA driver for JDBC.

https://communities.sas.com/t5/Developers/How-can-I-use-SAS-9-4-jdbc-driver-in-the-Spring-boot-application/m-p/562719/highlight/true#M408

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Fri, 12 Jul 2019 15:05:53 GMT

No thoughts immediately come to mind as to why your insert would have issues, but I do want to comment on you driver choice. Why use the MVA driver and define a library for SAS Share instead of using the SAS Share driver and connecting to that service directly?

Re: EG 8.1 and Studio Integration for tasks - custom tasks?

FriedEgg — Wed, 26 Jun 2019 17:54:40 GMT

I hope that this is very close to 'next' on the roadmap!

Re: Macro for loop hangs exactly at 27th cycle

FriedEgg — Wed, 26 Jun 2019 15:40:57 GMT

Ok, the best next step I can think of is to try to get some additional logging, from when it hangs.

When you submit from the command line, I want you to specify the following command line option:

-LOGPARM "WRITE=IMMEDIATE"

In your SAS program, let's also increase the log verbosity by adding the following line of code at the top of the program

OPTIONS MLOGIC MLOGICNEST MPRINT MPRINTNEST MSGLEVEL=I;

In your HTTP Procedure call you use the debug statement. Let's also increase the level

PROC HTTP [...];
  DEBUG LEVEL = 3;
RUN;

This all will produce a significantly larger log file, but specifically, what we care about most is if any additional log information is produced when the loop gets 'stuck' and the process appears to hang. As well as any potential logging produced when you, I assume, terminate the process.

Re: Macro for loop hangs exactly at 27th cycle

FriedEgg — Tue, 25 Jun 2019 16:05:20 GMT

What interface are you using to submit the SAS code?

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Mon, 24 Jun 2019 23:11:59 GMT

You have three better options (in order of my opinion):

1) Define the libraries in metadata

2) Define the libraries in the jdbc connection. Something like the following

3) Put the libname into the application server autoexec

spring.datasource.connectionProperties: librefs="appdev 'C:\foo\bar\data'"

Re: Proc Ginside with Shapefile that has multiple overlapping polygons, How to ID each polygon

FriedEgg — Sun, 09 Jun 2019 05:37:33 GMT

Using the polygon package for IML in place of PROC GINSIDE for calculating point intersection with multiple overlapping polygons

This notebook utilizes the polygon package for IML created by Rick Wicklin in order determine whether any of a set of points are inside a set of non-simple (intersecting) polygons. In this example we have two overlapping shapes and five points.

https://github.com/FriedEgg/Papers/blob/master/Random/Alternative%20to%20PROC%20GINSIDE%20for%20non-simple%20(intersecting)%20polygons.ipynb

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Wed, 05 Jun 2019 21:20:51 GMT

This shows up as a info line in the log, to me. Possibly a difference in our versions of SAS (I am using 9.4m6).

https://github.com/FriedEgg/SAS-JDBC-Spring-Boot-Example

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Wed, 05 Jun 2019 21:19:20 GMT

It is definitely possible, but it's important to note that the JDBC driver isn't exactly... complete... (by which, I mean, a number of features that other databases more commonly used in this context are missing, such as the various timeouts)

The biggest issue you're going to have with something like mybatis is that automapping uses the method ResultSetMetadata.getColumnLabel, which, in SAS returns the column label, naturally. But in general it returns the column name alias. This difference in behavior causes mybatis to fail and this can be corrected by setting the configuration property use-column-label=false.

mybatis.configuration.use-column-label=false

Re: How can I use SAS 9.4 jdbc driver in the Spring boot application

FriedEgg — Thu, 30 May 2019 21:05:28 GMT

Make sure you have all the correct libraries in your classpath:

log4j.jar
sas.core.jar
sas.security.sspi.jar
sas.svc.connection.jar

In your application.properties

spring.datasource.url=jdbc:sasiom://workspace.server.hostname.com:8591
spring.datasource.username=myusername
spring.datasource.password=mypassword
spring.datasource.driver-class-name=com.sas.rio.MVADriver

DemoApplication.java

package com.github.friedegg.demo;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@SpringBootApplication
public class DemoApplication {

  public static void main(String[] args) {
    SpringApplication.run(DemoApplication.class, args);
  }

  @Bean
  public CommandLineRunner commandLineRunner(ApplicationContext ctx) {
    return args -> {
      DataSource ds = (DataSource)ctx.getBean("dataSource");
      JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
      String SQL = "select * from sashelp.class";
      List<Student> students = jdbcTemplate.query(SQL, new SashelpClassMapper());
      for (Student student : students) {
        System.out.printf("%s is %d years old.%n", student.name, student.age);
      }
    };
  }

  class Student {
    private String name;
    private Integer age;

    public Integer getAge() {
      return age;
    }

    public void setAge(Integer age) {
      this.age = age;
    }

    public String getName() {
      return name;
    }

    public void setName(String name) {
      this.name = name;
    }
  }

  class SashelpClassMapper implements RowMapper<Student> {
    public Student mapRow(ResultSet rs, int rn) throws SQLException {
      Student student = new Student();
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      return student;
    }
  }
}

Console Output:

Alfred   is 14 years old.
Alice    is 13 years old.
Barbara  is 13 years old.
Carol    is 14 years old.
Henry    is 14 years old.
James    is 12 years old.
Jane     is 12 years old.
Janet    is 15 years old.
Jeffrey  is 13 years old.
John     is 12 years old.
Joyce    is 11 years old.
Judy     is 14 years old.
Louise   is 12 years old.
Mary     is 15 years old.
Philip   is 16 years old.
Robert   is 12 years old.
Ronald   is 15 years old.
Thomas   is 11 years old.
William  is 15 years old.

Re: SAS workspace connection with python using IOM Bridge

FriedEgg — Fri, 24 May 2019 14:59:45 GMT

pywin32 is a very finicky library. Make sure you have the correct version installed for your version of Python and bittedness.

Use this link to get a good version of pywin32 and see if that resolves your issue.

https://github.com/mhammond/pywin32/releases

Re: Using files stored in google drive in SAS (never used google drive for SAS programming before)

FriedEgg — Fri, 24 May 2019 14:39:28 GMT

As @Kurt_Bremser says, you aren't using the correct URL, so the response you are getting should just be HTML and not the file you actually want.

It is not a simple operation to do what you want and I'd recommend that you use one of the many command line tools available for downloading files from Google Drive instead of attempting an implementation yourself using PROC HTTP (though certainly not impossible).

https://github.com/gdrive-org/gdrive