Getting data into SAS Visual Analytics and working with it there

[nicwoyak]

Suppose in the Develop SAS Code part of SAS Viya, I have a table that I've generated using PROC SQL. I want to make this table accessible to the SAS Data Explorer so that I can utilize it in SAS Visual Analytics. What is the best way I can do this?

Related to this, is there a way in SAS Visual Analytics to perform joins and calculated columns using code instead of the GUI's? I would have to join about 4 tables and make a dozen calculated columns which would be a lot of time and clicks compared to doing the same thing with a PROC SQL statement. Assuming there is a way to do what I had mentioned above, it may work for certain applications, but it might not necessarily have the active, reloadable connection to database data that some applications require.

[Panagiotis]

Suppose in the Develop SAS Code part of SAS Viya, I have a table that I've generated using PROC SQL. I want to make this table accessible to the SAS Data Explorer so that I can utilize it in SAS Visual Analytics. What is the best way I can do this?

You will need to upload the data to CAS by uploading the table into one of your CASLIBS. Since your using Studio this video should help. It runs through the entire process.

Understanding Caslibs and Loading Data in SAS® Viya™

Here is the documentation: Load Data to a Caslib

Once the data is loaded into CAS you can work with it in Visual Analytics since it needs an in-memory table to create your visualizations.

Related to this, is there a way in SAS Visual Analytics to perform joins and calculated columns using code instead of the GUI's?

From my work with SAS Visual Analytics, you can perform joins and calculated columns inside Visual Analytics, however that's not the tool's main purpose. Like you said, it would take many clicks to do so. I personally like to do all my data prep using SAS or Python, then taking the finalized data and uploading it to CAS.

Your best bet is to perform the joins using a programming language of your choice (SAS, SQL, Python, R. Open source languages will work as long as it is set up with your admin and you install the SWAT package) and perform the data prep. Once the data is prepped you can save the physical file in a CASLIB and load that table into memory, or you can do it using code. It allows you the flexibility to do it as you see fit. 

I noticed you said you plan on using PROC SQL. How big is your data? Is it currently in CAS or are you working with it as a SAS data set? Be aware that PROC SQL does not run distributed in CAS. So if your data is big and it is already in CAS you might want to think about using PROC FEDSQL. Here is a quick blog: SAS® Viya®: How to Emulate PROC SQL Using CAS-Enabled PROC FedSQL.

However, if you don't need the distributed power of CAS you can stick to PROC SQL.

Assuming there is a way to do what I had mentioned above, it may work for certain applications, but it might not necessarily have the active, reloadable connection to database data that some applications require.

I wasn't 100% clear on this part. Which applications? If you write code you could connect to the database, pull the data, clean it, then upload it to CAS. You can run that program and it should do that.

Hope this helps. I can create a quick demo program if the video doesn't help.

- Peter

[nicwoyak]

Thank you for the response. I see now where I was going wrong with uploading data to CAS, I stopped at a proc casutil; load data= statement, but needed to have a proc casutil; save casdata= to complete the process.

I'll keep your suggestion in mind if the data gets too big for PROC SQL.

We would like to have some dashboard style reports where the object is periodically reloaded from the source data. If these objects used data from a database connection, there would be new records continually coming in. Perhaps some of the filters utilize the system clock for a rolling horizon effect. If the data I referenced was pulled, cleaned, and uploaded to SAS wouldn't it be static to that time compared to an object that references data from a database connection. Maybe there is a way to do all the preparation programmatically? 

[Panagiotis]

Yes if you are pulling the data from the database, cleaning/prepping it, then uploading it to CAS it would be static. It sounds like you need to schedule a job on your program. That way the program will run, prep the data, overwrite the CAS table on a schedule. Scheduling jobs is permission dependent, so you might need to speak with someone who has those permissions.

Here is a blog that might help: https://communities.sas.com/t5/SAS-Communities-Library/How-to-run-SAS-Studio-5-program-on-a-schedule...

I'd recommend testing it on a tiny program that uploads data to CAS. I'd create a basic report in VA on that data. Once that process is complete, i'd add data to that table in the program, run the job again, look at the VA report and make sure everything works.

Hope this help. Love to hear the outcome.

- Peter

[Panagiotis]

Here is a quick demo using the sashelp.cars table. This should work for you since you should have the sashelp.cars table. I recommend trying it one step at a time. I added a few extra steps in the code so you can physically see what is happening to the caslib. 

You do need to have permission to write to a caslib. So if you switch the caslib make sure you have permission to write in it.

/*Make a connection to the CAS server. Make the casuser caslib active (or whichever caslib you want)*/
cas sess sessopts=(caslib=casuser); 

/*View the available physical files in the casuser caslib*/
proc casutil incaslib=casuser;
	list files;
quit;


/*view the available in-memory tables in the casuser caslib*/
proc casutil incaslib=casuser;
	list tables;
quit;


/*Make a new library using the casuser caslib*/
libname new cas caslib=casuser;


/*Data prep. Create a new table in CAS that contains one new column. You can do your joins here. The new table will be placed in the new library,
which is in CAS, casuser caslib*/
proc sql;
create table new.newtable as
	select *, mean(MPG_city,MPG_Highway) as Avg
	    from sashelp.cars;
quit;


/*View the files and in-memory table in the casuser caslib*/
/*When create a new cas table, by default the table is only in-memory. You will not see a physical file
  of the new.newtable, only an in-memory table*/
proc casutil incaslib=casuser;
	list files;
	list tables;
quit;


/*SAS Visual Analtyics should now be able to access 'newtable' in the casuser caslib*/

/*However you still don't have a physical copy of that new table you created. Also, the in-memory table you loaded is only active for
your current session. When you exit or timeout the in-memory table is cleared. You have two options:
1. Save the in-memory table as a physical copy in the caslib
2. Promote the in-memory tablet to 'global' scope. Global scope means anyone who has access to that caslib can see that table. In casuser,
only you should have access.*/

/* To save the table to a physical copy*/
proc casutil;
	save casdata='newtable' incaslib='casuser'
	     casout='newtable.sashdat' outcaslib='casuser';
quit;


/* To promote the in-memory table to global scope*/
proc casutil;
	promote casdata='newtable' incaslib='casuser';
quit;



/*See the new files and changes*/
proc casutil incaslib=casuser;
	list files;
	list tables;
quit;

I can now access 'newtable' in SAS Visual Analytics:

[nicwoyak]

Thank you for the replies on this. I think this approach should end up working for me. I had to put in a replace clause in the statement that saves the table to a physical copy for when the data is refreshed. I am getting a similar error "The target table newtable of the promotion already exists. Please specify a different name." on the statement

proc casutil;
promote casdata='newtable' incaslib='casuser';
quit;

I see that this statement does not also have a replace clause and recommends dropping then doing a new promote. I'm not clear on how to do this, I've run into errors like "There is no session-scope table newtable in caslib" or once that reads the same as above. What would be a way to modify the program to adjust for replacing when data is refreshed?