BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
acordes
Rhodochrosite | Level 12

It works except that it does not update the graph.

for refreshing the graph I need to clear the URL, enter, and paste it in again.

@Renato_sas @XavierBizoux , any idea how to achieve it?

 

the bar chart performs filter actions on the other 2 DDC objects.

the invisible below uses json to write the filtered data to a promoted table.

 

the custumized sgplot takes this table as an input.

it works but the table promote ddc is faster and second the sgplot chart does not update until I do the trick described at the beginning.

 

honeycomb.png

1 ACCEPTED SOLUTION

Accepted Solutions
XavierBizoux
SAS Super FREQ

Hi, 

I'm happy to see that you are using what we produced. 

If I understand properly, you have created a job which generates a graph using proc sgplot. This job URL is used as the URL for the DDC object. The job returns directly the graph to _webout destination as an html file. 

If all this is correct, the problem with this technique is that there is no way to refresh the DDC object because there is no event listener in the output file generated by the job. As a result, there is no way to detect when a parameter is updated.

No comes the solutions. We have two approaches:

  • split the task by creating a simple web page which will be responsible of handling the call to the job and displaying the graph. 
  • embed the JavaScript files and code to handle the parameter changes in the html returned by the job. 

To split the tasks, you should have two components.

  1. A specific web page which handles the collection of the parameters passed from the VA report and receives a pointer to the generated graph. 
  2. The SAS Viya Job which will execute the SAS code, create a graph, store the graph in the SAS Content Server and finally returns the location of that image file (using proc json). If you don't want to store the image in the SAS Content server, you may convert the png image generated by the sgplot procedure into base64 and return the base64 string in the json object (conversion is described here)

In the second approach, you should use the ODS html destination and write a script tag that will basically resubmit the job if new parameter values are passed to the DDC object. 

 

While the two approaches should work, I've not tested them. My preferred approach would be the first one. Separating the two tasks would make it easier to maintain and to standardize if you want to reuse in multiple reports with different jobs. 

If you need help in implementing one or the other, please don't hesitate. 

 

Regards,

 

Xavier

Xavier BIZOUX
Advisory Technical Architect ● Global Enablement and Learning
Technology Transfer and Governance, R&D

View solution in original post

14 REPLIES 14
Renato_sas
SAS Employee

Hi @acordes. Why are you using two DDC objects? As you noticed, their execution is asynchronous and you can't guarantee the order they will be executed. Couldn't you just use one DDC to send the table to your SAS Job that will generate the chart? If you need the table to be saved, you could do that from the SAS Job itself.

acordes
Rhodochrosite | Level 12
Hi . What you recommend was my first failed intent. For the first DDC, the one that saves the table, I use the parameters which are passed by adding them in the advanced filter role. The job then calls the form which prepares for the json output.
I use the _action=form parameter in the job itself and with the _form parameter I link to the helper htm. It's all copied from your blog posts or Xavier's blog posts about job execution.
So far so good. But when I attach at the end the sgplot statement nothing happens.
I tried using the _action=form, execute parameter and the _output_type=html parameter in the job without success.
That's the reason why I split into 2 DDCs.
Renato_sas
SAS Employee

The output of proc sgplot is an image. There is an example in the documentation, and _OUTPUT_TYPE can be gif, jpeg, svg, or png:

SAS Help Center: Samples

 

Have you tried that?

acordes
Rhodochrosite | Level 12

It still does not work @Renato_sas .

I don't know where to send the output from the proc sgplot. On its own it works but attached to the snippet that creates the table via json, it's not shown.

the log to me does not reveal much.

If I send a html via ods command the file and its png get written correctly to the specified path.

But this machine is not reachable via VA so the workaround to grab it via web content does not work either.

The problem is that I don't understand html at all, what I do is copy/paste trial and error.

I don't understand why viya makes it so complicated to have ods graphs within Visual Analytics. It's crazy, you can insert third party visualizations easier than sas own powerful ods graphics. 

 

* The JSON file this job expects contains the modified JSON message;
* that SAS Visual Analytics sent to the Data-Driven Content object.;
* It contains transformed data and additional column metadata.     ;

*==================================================================;
* Initialization;
*==================================================================;

* This allows for unconventional column names (e.g.: spaces, etc.);
options VALIDVARNAME=any;

* This allows for the stopOnError macro function to run the sas commands after an error occurs;
options NOSYNTAXCHECK;

%macro stopOnError(msg);
  %put &=SYSRC  &=SYSCC  &=SYSFILRC  &=SYSLIBRC  &=SYSERR  SYSERRORTEXT=%superq(syserrortext)  &=MSG;
  %if (&msg eq ) %then %let msg=%superq(syserrortext);
  %if (&syserr > 6 or &msg ne ) %then %do;
    proc json out=_webout nosastags nopretty nokeys;
	  write open object;
	  write values "success" false;
	  write values "retcode" &SYSERR;
	  write values "message" "&MSG";
	  write close;
	run;
    cas mySession terminate;
    %let SYSCC=0;
    %abort cancel;
  %end;
%mend stopOnError;

%macro checkParams;
	%if (not %symexist(castab)) %then %stopOnError(Missing parameter CASTAB);
%mend checkParams;
%checkParams;

* Connect to CAS and assign the CASUSER library;
options cashost="xxxxx.yYy.es" casport=xxXX;
cas mySession;
%stopOnError();

libname casuser CAS caslib="casuser";
%stopOnError();

* Retrieve JSON data from uploaded file;
filename vaJSON filesrvc "&_WEBIN_FILEURI";
%stopOnError();

* Use the JSON engine to provide read-only sequential access to JSON data;
libname jsonLib json fileref=vaJSON;
%stopOnError();

* Create table to assist creation of JSON map file;
* Replace blank spaces in column names with underscore (_);
* Output table contains column name, label, type, format, format width, and format precision;
%macro prepColMetadata;
  %if %sysfunc(exist(jsonLib.columns_format)) %then %do;
	proc sql noprint;
	  create table col_metadata as (
	    select 
	         c.ordinal_columns, translate(trim(c.label),'_',' ') as column, 
	         c.label, 
	         c.type4job as type,
	         f.name4job as fmt_name,
	         f.width4job as fmt_width,
	         f.precision4job as fmt_precision
	    from jsonLib.columns c left join jsonLib.columns_format f
	    on c.ordinal_columns = f.ordinal_columns
	  );
	quit;
	%stopOnError();
  %end;
  %else %do;
    * table columns_format does not exsist;
    * all columns are strings (no format object in the JSON structure);
	proc sql noprint;
	  create table col_metadata as (
	    select 
	         c.ordinal_columns, translate(trim(c.label),'_',' ') as column, 
	         c.label, 
	         c.type4job as type,
	         "" as fmt_name,
	         . as fmt_width,
	         . as fmt_precision
	    from jsonLib.columns c 
	  );
	quit;
	%stopOnError();
  %end;
%mend;

%prepColMetadata;

filename jmap temp lrecl=32767;
%stopOnError();

* Create JSON map file to be used to read VA JSON with proper labels, formats, types, etc.;
data _null_;
  file jmap;
  set col_metadata end=eof;
  if _n_=1 then do;
    put '{"DATASETS":[{"DSNAME": "data_formatted","TABLEPATH": "/root/data","VARIABLES": [';
  end;
  else do;
    put ',';
  end;
  if fmt_name ne "" then
    line=cats('{"PATH":"/root/data/element',ordinal_columns,
              '","NAME":"',column,
              '","LABEL":"',label,
              '","TYPE":"',type,
              '","FORMAT":["',fmt_name,'",',fmt_width,',',fmt_precision,']}');
  else
    line=cats('{"PATH":"/root/data/element',ordinal_columns,
              '","NAME":"',column,
              '","LABEL":"',label,
              '","TYPE":"',type,'"}');
  put line;
  if eof then do;
    put ']}]}';
  end;
run;
%stopOnError();

* Reassign JSON libname engine to provide read-only sequential access to JSON data, now with map;
libname jsonLib json fileref=vaJSON map=jmap;
%stopOnError();

*==================================================================;
* Main Processing;
*==================================================================; 

* Add table to CAS lib casuser (session scope);

* Add table to CAS lib casuser (session scope);
data casuser.&CASTAB._TMP;
	set jsonLib.data_formatted;
run;
%stopOnError();

options nofmterr;
data casuser.&CASTAB._TMP;
set casuser.&CASTAB._TMP ;
where meses_efectivos le 84 and actual_mileage le 200000;
keep keep meses_efectivos actual_mileage _brand _gama;
run;
%stopOnError();


* Put table on its final destination: casuser and global scope;
proc casutil;
  droptable casdata="&CASTAB" incaslib="casuser" QUIET;
  promote casdata="&CASTAB._TMP" incaslib="casuser" 
          casout="&CASTAB" outcaslib="casuser" DROP;
run;
quit;
%stopOnError();

*  Declare input parameters;

* This allows for unconventional column names (e.g.: spaces, etc.);
options VALIDVARNAME=any;

caslib _ALL_ assign;
options casdatalimit=all;

data test;
set casuser.bees;
where meses_efectivos le 84 and actual_mileage le 200000;
run;

ods graphics on;
ods output fitplot=casuser._HexMap;  /* write graph data to a data set */
proc surveyreg data=test plots(nbins=20 weight=heatmap)=fit(shape=hex);
   model meses_efectivos = actual_mileage;
run;
ods select all;

data _hexmap;
set casuser._hexmap;
run;

 
/* ODS GRAPHICS ON / height=800 width=1400 reset=all border=off antialiasmax=2500 tipmax=2500  */
/* imagemap=on imagename='bees and miles';  */
/* ods html path="/caslibs/danalytics/" (url=none)  */
/* body="bees miles.html"  */
/* (title='bees fly miles'); */

ODS GRAPHICS ON / height=800 width=1400 reset=all;
proc sgplot data=_HexMap;
   polygon x=XVar y=YVar ID=hID / colorresponse=WVar fill 
                                    colormodel=TWOColorRamp dataskin=sheen ;
run;

ods graphics off;

*==================================================================;
* Finalization;
*==================================================================;

cas mySession terminate;

* The return code to is sent back to the calling client (Data-Driven Content object) in JSON format;
proc json out=_webout nosastags nopretty nokeys;
  write open object;
  write values "success" true;
  write values "retcode" 0;
  write values "message" "success";
  write close;
run;

para.png

 

 

XavierBizoux
SAS Super FREQ

Hi, 

I'm happy to see that you are using what we produced. 

If I understand properly, you have created a job which generates a graph using proc sgplot. This job URL is used as the URL for the DDC object. The job returns directly the graph to _webout destination as an html file. 

If all this is correct, the problem with this technique is that there is no way to refresh the DDC object because there is no event listener in the output file generated by the job. As a result, there is no way to detect when a parameter is updated.

No comes the solutions. We have two approaches:

  • split the task by creating a simple web page which will be responsible of handling the call to the job and displaying the graph. 
  • embed the JavaScript files and code to handle the parameter changes in the html returned by the job. 

To split the tasks, you should have two components.

  1. A specific web page which handles the collection of the parameters passed from the VA report and receives a pointer to the generated graph. 
  2. The SAS Viya Job which will execute the SAS code, create a graph, store the graph in the SAS Content Server and finally returns the location of that image file (using proc json). If you don't want to store the image in the SAS Content server, you may convert the png image generated by the sgplot procedure into base64 and return the base64 string in the json object (conversion is described here)

In the second approach, you should use the ODS html destination and write a script tag that will basically resubmit the job if new parameter values are passed to the DDC object. 

 

While the two approaches should work, I've not tested them. My preferred approach would be the first one. Separating the two tasks would make it easier to maintain and to standardize if you want to reuse in multiple reports with different jobs. 

If you need help in implementing one or the other, please don't hesitate. 

 

Regards,

 

Xavier

Xavier BIZOUX
Advisory Technical Architect ● Global Enablement and Learning
Technology Transfer and Governance, R&D
acordes
Rhodochrosite | Level 12

Hi @XavierBizoux , thanks for your instructive reply.

If I knew more about html i could properly make it work for me with your guideline.

Therefore I'll mark your reply as solution although I get out of it for the moment waiting for you or Renato to write an article about it: "how to use customized ods graphs within VA using DDC, job execution and json" 🙂

 

acordes
Rhodochrosite | Level 12

And yes, @XavierBizoux and @Renato_sas , many of my reports that went live incorporate your tricks to have VA updating tables via job execution or running scoring code in the back for later use in the report itself. This feature makes the report highly dynamic and the users have a real added value because the report turns into an application for them.

Thanks for all your input so far, but as I've said before I lack too much base knowledge in html to put your recommendations into practice.

To have customized ods graphs within VA is the last functionality I'm looking for.

XavierBizoux
SAS Super FREQ

Hi, 

 

I've been looking for more information around this topic. 

I think you might be interested by this example. 

https://go.documentation.sas.com/doc/en/pgmsascdc/v_016/jobexecug/n0c2g3xhyfv4s9n11mhwm3cktyk0.htm

Basically you would include the img tag as described in the documentation: 

<img src="http://host:port/SASJobExecution/?_program=/Folder/Return SGPLOT Image Output&_action=execute">

When the data are passed back from the report to the DDC object, the JavaScript code should delete the existing image and then add a new img tag with the parameters you want to pass. That should cause the rendering of the sgplot.

As you suggest, I will try to write a blog around this in the near future. I hope it will not be too late for you.

Regards,

Xavier

Xavier BIZOUX
Advisory Technical Architect ● Global Enablement and Learning
Technology Transfer and Governance, R&D
acordes
Rhodochrosite | Level 12

I'm looking forward to reading a new fascinating how-it-works blog from you.

The image tag option is promising, but as I'd said, my html skills are near 0.

Where should I insert the img tag? I thought it has to show up where the last status update message "Done!" is sent to the DDC object.

<img src="https://xxx.xx.xx.xxx/SASJobExecution/?_program=%2FPublic%2Frenato%2Flabor%2Ftest_sgplot_v2&_action=...">

 

<!DOCTYPE html>
<!--
Copyright 2020 SAS Institute Inc.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<html>
<head>
	<title>Create CAS table from VA Data</title>
	<script type="text/javascript" src="https://xxxxxx/htmlcommons/util/messagingUtil.js"></script>
	<script type="text/javascript" src="https://xxxxxx/htmlcommons/util/contentUtil.js"></script>
	<script type="text/javascript" src="https://xxxxxx/htmlcommons/util/jobUtil.js"></script>
    
    <style>
		html {
			overflow: hidden;
		}
        #JobResults {
            position: relative;
			font-family: "Albany AMT";
			font-size:10pt;
        }
    </style>

    <script>
		"use strict";
    	window.$ = window.parent.$;
		
		var _job_output_cas_table_v2 = null; 	// example: "CARS_COPY";
		var _job_executing_message_v2 = null; 	// example: "Waiting for job to finish...";
		var _job_name_v2 = null; 				// example: "/Public/Jobs/SAS Communities/HelloCASWorld"
		
        function onDataReceived(resultData)
        {
            if (resultData) {
				var resultName = resultData.resultName;
				
				var selections = va.contentUtil.initializeSelections(resultData); // good practice to remove eventual brush columns

				if (resultData.columns.length == 0) {
					// it needs at least one column and one row
					document.getElementById("JobResults").innerHTML = "";
					va.messagingUtil.postInstructionalMessage(resultName, "Please, assign roles");
					return;
				}
				
				var vaParameters = va.contentUtil.getVAParameters(resultData);
				_job_output_cas_table_v2  = vaParameters._job_output_cas_table_v2;
				_job_executing_message_v2 = vaParameters._job_executing_message_v2;
				_job_name_v2              = vaParameters._job_name_v2;
				if (!_job_executing_message_v2) _job_executing_message_v2 = "Executing job...";
				if (!_job_output_cas_table_v2 || !_job_name_v2) {
					// missing one or more parameters
					document.getElementById("JobResults").innerHTML = "";
					va.messagingUtil.postInstructionalMessage(
						resultName, 
						"Please, make sure the following parameters are being passed to this object:\n"+
						"(1) _job_name_v2\n"+
						"(2) _job_output_cas_table_v2\n"+
						"(3) _job_executing_message_v2 (optional)"
					);
					return;
				}
				
				document.getElementById("JobResults").innerHTML = _job_executing_message_v2;
				callJob(resultData)
					.done(function(jobOutput){
						if (jobOutput.success) {
							<img src="https://xxxxx/SASJobExecution/?_program=%2FPublic%2Frenato%2Flabor%2Ftest_sgplot_v2&_action=execute">
							document.getElementById("JobResults").innerHTML = "Done!";
							setTimeout(() => {document.getElementById("JobResults").innerHTML = "";}, 2000);
												}	
						else {
							document.getElementById("JobResults").innerHTML = "Job finished with ERROR (see console for details)";
						}
					})
					.fail(function(jqXHR, textStatus, errorThrown){
						document.getElementById("JobResults").innerHTML = "Job failed to execute (see console for details)";
					});
				
            }
        }
		
	    function callJob(resultData) {
			va.jobUtil.PrepareVADataForSASJobs (resultData);
			var stringifiedJSON = JSON.stringify(resultData);
	
			// Your large JSON object as a Blob - Blob is like a file, and the POST method sends the file to the server
			var jsonBlob = new Blob([stringifiedJSON], {type: 'text/plain'});
			
			// Create form with input parameters
			var formData = new FormData();
			formData.append("_program", _job_name_v2);
			formData.append("_action", "execute");
			formData.append("_output_type", "json");
			formData.append("myjsonfile", jsonBlob);
			formData.append("castab", _job_output_cas_table_v2);
			//formData.append("_debug", "log"); formData.append("_output_type", "text");
	
			return $.ajax({
					method: "POST", 
					url: "/SASJobExecution/", 
					data: formData, 
					contentType: false, // do not send content-type
					processData: false, // do not transform data to fit to the default content-type application/x-www-form-urlencoded
					headers:{"X-CSRF-TOKEN": "$CSRF$", "Accept":"application/json"}
				})
				.done(function(jobOutput){
					if (jobOutput.success) {
						console.log("Job executed with success!");
					}		
					else {
						console.log("Job executed with ERROR");
						console.log("jobOutput=", jobOutput);
					}
				})
				.fail(function(jqXHR, textStatus, errorThrown){
					console.log("************** JOB FAILED ***************");
					console.log("jqXHR: ",jqXHR);
					console.log("textStatus: ",textStatus);
					console.log("errorThrown: ",errorThrown);
				});
	    }
	    
        va.messagingUtil.setOnDataReceivedCallback(onDataReceived);
    </script>
</head>
<body>
    <div id="JobResults"></div>
</body>
</html>

 

 

 

Renato_sas
SAS Employee

Hi @acordes

I think you have mixed things up. The template of job you are using was designed to write to a CAS table. It doesn't generate any visual output, so the SAS Job returns a small JSON object containing the status of the job execution. If your job is supposed to generate a visible output to be displayed in VA, you should not return those JSON objects. 

I've used the example called HelloBigWorldFormatted as the starting point and replaced the proc PRINT in the "Main Processing" section with the proc SGPLOT and it worked:

* The JSON file this job expects contains the modified JSON message;
* that SAS Visual Analytics sent to the Data-Driven Content object.;
* It contains transformed data and additional column metadata.     ;

*==================================================================;
* Initialization;
*==================================================================;

* This allows for unconventional column names (e.g.: spaces, etc.);
options VALIDVARNAME=any;

* Retrieve JSON data from uploaded file;
filename vaJSON filesrvc "&_WEBIN_FILEURI";

* Use the JSON engine to provide read-only sequential access to JSON data;
libname jsonLib json fileref=vaJSON;

* Create table to assist creation of JSON map file;
* Replace blank spaces in column names with underscore (_);
* Output table contains column name, label, type, format, format width, and format precision;
%macro prepColMetadata;
  %if %sysfunc(exist(jsonLib.columns_format)) %then %do;
	proc sql noprint;
	  create table col_metadata as (
	    select 
	         c.ordinal_columns, translate(trim(c.label),'_',' ') as column, 
	         c.label, 
	         c.type4job as type,
	         f.name4job as fmt_name,
	         f.width4job as fmt_width,
	         f.precision4job as fmt_precision
	    from jsonLib.columns c left join jsonLib.columns_format f
	    on c.ordinal_columns = f.ordinal_columns
	  );
	quit;
  %end;
  %else %do;
    * table columns_format does not exsist;
    * all columns are strings (no format object in the JSON structure);
	proc sql noprint;
	  create table col_metadata as (
	    select 
	         c.ordinal_columns, translate(trim(c.label),'_',' ') as column, 
	         c.label, 
	         c.type4job as type,
	         "" as fmt_name,
	         . as fmt_width,
	         . as fmt_precision
	    from jsonLib.columns c 
	  );
	quit;
  %end;
%mend;

%prepColMetadata;

filename jmap temp lrecl=32767;

* Create JSON map file to be used to read VA JSON with proper labels, formats, types, etc.;
data _null_;
  file jmap;
  set col_metadata end=eof;
  if _n_=1 then do;
    put '{"DATASETS":[{"DSNAME": "data_formatted","TABLEPATH": "/root/data","VARIABLES": [';
  end;
  else do;
    put ',';
  end;
  if fmt_name ne "" then
    line=cats('{"PATH":"/root/data/element',ordinal_columns,
              '","NAME":"',column,
              '","LABEL":"',label,
              '","TYPE":"',type,
              '","FORMAT":["',fmt_name,'",',fmt_width,',',fmt_precision,']}');
  else
    line=cats('{"PATH":"/root/data/element',ordinal_columns,
              '","NAME":"',column,
              '","LABEL":"',label,
              '","TYPE":"',type,'"}');
  put line;
  if eof then do;
    put ']}]}';
  end;
run;

* Reassign JSON libname engine to provide read-only sequential access to JSON data, now with map;
libname jsonLib json fileref=vaJSON map=jmap;

*==================================================================;
* Main Processing;
*==================================================================; 

proc sgplot data=jsonLib.data_formatted;
  vbar origin / response=invoice;
run;

*==================================================================;
* Finalization;
*==================================================================;

* No finalization required in this simple example;
acordes
Rhodochrosite | Level 12

That's it! And it even updates.

It works, I can't believe that it finally does what I had in mind.

With DDC you can leverage third-party visualizations, update tables, score data and show customized ods graphs.

When you add what already comes shipped as graph objects with Visual Analytics plus the possibilities of using graph builder then you end up having impressive visualization power.

 

1000 thanks to @Renato_sas and @XavierBizoux 

 

solved.png

Renato_sas
SAS Employee

@acordes, I'm glad to hear that. 

Please don't forget to mark it as solved to help others 😉

BTW, was it as simple as I said or did you have to do something else?

acordes
Rhodochrosite | Level 12

@Renato_sas I can only give "likes" because I had marked it as solved already with the guideline from Xavier.

Following your steps as of the original helloworld blog post it was easy to accomplish, at least the hellobigworld job. His brother hellobigworldformatted resists from being executed, with more time I'll have a deeper look.But the hellobigworld just performs fine, knowing the data items I pass via DDC I can easily rename it code wise in the job.

Renato_sas
SAS Employee

@acordes, I had not noticed that you had already marked it as solved. Yes, I don't think you can change it, but as long as it helped you accomplish your goals, this is what is important.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1896 views
  • 5 likes
  • 3 in conversation