Choose Your Own Adventure: Uploading Data Sets to SAS Viya Jobs

SAS Viya jobs have many different applications. In previous articles, I’ve discussed how to share jobs using SAS Visual Analytics and how to jump start your job development using samples, where I looked at an example that involved uploading a single CSV file to the job. In this article, I’ll discuss how to create a job that allows the end user to upload more than one data set.

How Uploaded Data is Passed to Jobs

In my previous article, I showed how to modify a sample job to create a simple map using an uploaded CSV file. In that example, the uploaded CSV file was passed from the form to the job program using the following macro variables:

_WEBIN_FILENAME: the name and location of the uploaded file

_WEBIN_FILEEXT: the file extension

_WEBIN_FILEURI: the URI of the uploaded file

When you upload multiple files, the number of files is stored in the _WEBIN_FILE_COUNT macro variable. Then each file is passed to the job as _WEBIN_FILENAMEn, _WEBIN_FILEEXTn, and _WEBIN_FILEURIn, where n is the number of the file that was uploaded.

For example, in a form where two files are uploaded, the following macro variables are assigned:

_WEBIN_FILE_COUNT: 2

_WEBIN_FILENAME1: name of first file

_WEBIN_FILEEXT1: extension of first file

_WEBIN_FILEURI1: URI of first file

_WEBIN_FILENAME2: name of second file

_WEBIN_FILEEXT2: extension of second file

_WEBIN_FILEURI2 : URI of second file

Comparing Data Sets

Let’s look at an example of how this works. I have two data sets that both contain data about students in a class, but there are a few differences between the two tables. I’d like to create a job that will allow me to compare the data sets and easily identify the differences.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

Starting with a Working Program

Like with all jobs, it’s a best practice to start with a working program. I’ll use the COMPARE procedure to compare these two data sets. Since they are both stored as CSV files, I’ll also have to first import them to the WORK library. I’ll use the ID statement to specify the Name variable should be used to match observations:

proc import datafile="/workshop/CLASS.csv" out=class
	dbms=csv replace;
	guessingrows=max;
run;

proc import datafile="/workshop/CLASS_NEW.csv" out=class_new
	dbms=csv replace;
	guessingrows=max;
run;

proc compare base=class compare=class_new;
	id name;
run;

Building the Form

Next, I want to create a job so that non-programmers can select their own files to compare. I’ll begin with the sample HTML form I discussed in my previous article. This sample form already includes one file upload input tag. I can simply copy and paste that to create another:

Note that I’ve also updated the name attribute of the input tag. The name value will get passed to the _WEBIN_NAME macro variable. Also notice the required attribute – this will require the user to select a file, or else the form will not allow submission.

I’ll also update some of the formatting and add some explanatory text to indicate what the job does:

Handling the Inputs

Now that the HTML form is created, I need to write code to handle the two uploaded files. I’ll begin by using the code from the sample job I discussed in my previous article as well.

This code does three things:

Validates the uploaded file is a CSV file
Creates a fileref pointing to the uploaded file
Imports the uploaded CSV file

I will modify this code by copying it to import a second file and update the _WEBIN_FILENAME and _WEBIN_FILEEXT macro variables to reference the first and second uploaded files. I’ll also update the filenames and table name references to match the first and second uploaded class files.

Here is the code that will handle the first uploaded file:

And here is the code that will handle the second uploaded file:

Now, I can just add the PROC COMPARE code, updating the input data to the imported class_1 and class_2 CSV files.

Putting it All Together

When I run the job, the form looks like this, with two file upload button buttons:

Clicking on the Choose File button will open a file selection dialogue box:

Because I added the required attribute on both the input tags in the HTML form, the form will not allow me to submit it without selecting both files.

When I submit the job, the PROC COMPARE results for the two uploaded CSV files will open in a new tab:

Conclusion

By building forms that allow users to upload their own data, you can easily generalize your jobs for a wide audience. You can use this technique to create programs that process, transform, or visualize any number of user-selected input tables, and then pass share end users with no programming experiences to easily enable them to generate reports, combine data sets, and more.