The SAS Output Delivery System and reporting techniques

STP ODS html drill down function with dependency

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

STP ODS html drill down function with dependency

Hi

 

I would like to make a proc tabulate with a parent-child dependency.

 

I 've tried to make a html format that contains the url to another detailed Stored Processn with url-parameters. This works fine except for the aggregation in proc tabulate ("I alt"). In this case the aggregation doesn't work because the then two html-url's beneath the name "Sedan" points to both BMW and Audi.

 

Do you have any suggestions how to do the aggregation right and still keep the format and the drill-down function ?

 

Proc tabulate woth html format.png

 

Best regards Phillip

Attachment

Accepted Solutions
Solution
‎07-05-2016 03:17 AM
SAS Super FREQ
Posts: 8,721

Re: STP ODS html drill down function with dependency

[ Edited ]

Hi:

  There is a rule of PROC REPORT that is going to be critical to understanding what is going on. PROC REPORT does NOT have a Program Data Vector like the DATA step. So you can't assume that the COMPUTE block for MAKE will be able to see the value for MAKE2. Here's the reason why your code won't work.

 

  PROC REPORT works from LEFT to RIGHT and builds the report row 1 report column and 1 report row at a time. So let's say you have a simplified COLUMN statement:

   column grpvar1 var2 var3 var4 var5;

 

  At the point in time when PROC REPORT is writing GRPVAR1 on the report row, it has NOT YET written var2, var3, var4 or var5. So that means the COMPUTE block for GRPVAR1 cannot refer to any item that appears to it's right on the COLUMN statement (which defines the report row). When PROC REPORT is writing GRPVAR1, it does not yet know what any of those other variable values are. But, at the point in time when PROC REPORT puts VAR5 on the report row, it knows what the value of all the previous items on the row are. But it only knows what it just put down. Once the row is written, PROC REPORT moves on to the next row and starts over.

 

  The good news is that when you have a COMPUTE block for VAR5, your CALL DEFINE statement in a COMPUTE block can change any item on the row.

 

  My advice is that you take a look at the attached screen shot and the way that I made the helper variables I needed. I did not use a "preprocessing" step like you did. I did everything in COMPUTE blocks. It is 6 of 1, etc, etc, which approach you choose. But the fact remains that however you do it, the COMPUTE block for MAKE will NOT be able to use the value in MAKE2. But inside a COMPUTE block for MAKE2, you could issue a URL for MAKE. I did not bother with a stored process, because 1) I don't have an image to play with right now and 2) I prefer to get things working using GOOGLE to make sure the "back half" of my URL is being built correctly and then once I get things working without a stored process, then I move everything to the world of stored processes.

 

  Hope this helps.

 

cynthia

 

create_dynamic_url_report.png

 

The full code:

ods _all_ close;
ods html file='c:\temp\cars_url.html' style=seaside;
proc report data=sashelp.cars spanrows;
   title '1) all helper columns displayed';
	where Make in("Audi" "BMW" "Mercedes-Benz");
	column make holdmake type holdtype useinurl driveTrain  weight length;
	define make / group f=$15.
       style(column)=Header;
    define holdmake / computed;
	define type / group f=$15.
	   style(column)=Header;
	define holdtype / computed;
    define useinurl / computed;
	define drivetrain / group f=$15.
	   style(column)=Header;
	define weight / mean f=comma9.2;
	define length / mean f=comma9.2;
	rbreak after / summarize;
	compute before make;
	  length xxx $15;
	  xxx = make;
	endcomp;
	compute before type;
	  length yyy $15;
	  yyy = type;
	endcomp;
	compute holdmake/character length=15;
	  holdmake = xxx;
	  if _break_ = ' ' then murl=catt('http://www.google.com/#q=',make);
	  else murl =  'http://www.google.com/#q=Average+MSRP+All+Cars';
	  call define('Make','url',murl);
	endcomp;
	compute holdtype/character length=15;
	  holdtype = yyy;
	  if _break_ = ' ' then turl=catt('http://www.google.com/#q=',holdmake,'+',holdtype);
	  call define('type','url',turl);
	endcomp;
	compute useinurl / character length =100;
	  ** show what will be used in the url;
	  useinurl = catt(holdmake,'+',holdtype);
	endcomp;
	compute drivetrain;
	  if _break_ = ' ' then durl=catt('http://www.google.com/#q=',holdmake,'+',holdtype,'+',drivetrain);
	  call define(_col_,'url',durl);
	endcomp;
    compute after;
	  Make = 'Total';
	  type = '~';
	  drivetrain = '~';
	  call define('Make','style','style=Header');
	  call define('type','style','style=Header ');
	  call define('drivetrain','style','style=Header ');
	endcomp;
run;

    
proc report data=sashelp.cars spanrows;
   title '2) helper columns hidden and report cleaned up';
 	where Make in("Audi" "BMW" "Mercedes-Benz");
	column make holdmake type holdtype driveTrain  weight length;
	define make / group f=$15.
       style(column)=Header;
    define holdmake / computed noprint;
	define type / group f=$15.
	   style(column)=Header;
	define holdtype / computed noprint;
	define drivetrain / group f=$15.
	   style(column)=Header;
	define weight / mean f=comma9.2;
	define length / mean f=comma9.2;
	rbreak after / summarize;
	compute before make;
	  length xxx $15;
	  xxx = make;
	endcomp;
	compute before type;
	  length yyy $15;
	  yyy = type;
	endcomp;
	compute holdmake/character length=15;
	  holdmake = xxx;
	  if _break_ = ' ' then murl=catt('http://www.google.com/#q=',make);
	  else murl =  'http://www.google.com/#q=Average+MSRP+All+Cars';
	  call define('Make','url',murl);
	endcomp;
	compute holdtype/character length=15;
	  holdtype = yyy;
	  if _break_ = ' ' then turl=catt('http://www.google.com/#q=',holdmake,'+',holdtype);
	  else turl = '_undef_';
	  call define('type','url',turl);
	endcomp;
	compute drivetrain;
	  if _break_ = ' ' then durl=catt('http://www.google.com/#q=',holdmake,'+',holdtype,'+',drivetrain);
	  else durl = '_undef_';
	  call define(_col_,'url',durl);
	endcomp;
    compute after;
	  Make = 'Total';
	  type = '~';
	  drivetrain = '~';
	  call define('Make','style','style=Header');
	  call define('type','style','style=Header{color=cxfaf3d4} ');
	  call define('drivetrain','style','style=Header{color=cxfaf3d4} ');
	endcomp;
run;

 ods html close;
 

 

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,721

Re: STP ODS html drill down function with dependency

Hi:
Since PROC REPORT will make URLs dynamically, I would probably have done this with PROC REPORT and avoided all the pre-processing and probably the format steps that you have. Look at this paper for an example:https://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf on pages 17 and 18 to see how the URL can be set directly in a COMPUTE block -- which would also give you a place to treat the summary URL different from the URL on every row.

cynthia
New Contributor
Posts: 3

Re: STP ODS html drill down function with dependency

Hi Cynthi

 

Thanks for the fast reply.

 

I've tried to make then url in proc report work but it seems that when you're making a compute variable, you can't reference columns that is defined as group. I need to reference these variables to transfer the hierarchy into the URL.

 

I've tried to make copies of the needed variables and place them lower in the column statement but it doesn't seem to work.

 

Do you have any idea how to reference another variabel that the one in the compute block?

 

Best regards 

Phillip

Attachment
Solution
‎07-05-2016 03:17 AM
SAS Super FREQ
Posts: 8,721

Re: STP ODS html drill down function with dependency

[ Edited ]

Hi:

  There is a rule of PROC REPORT that is going to be critical to understanding what is going on. PROC REPORT does NOT have a Program Data Vector like the DATA step. So you can't assume that the COMPUTE block for MAKE will be able to see the value for MAKE2. Here's the reason why your code won't work.

 

  PROC REPORT works from LEFT to RIGHT and builds the report row 1 report column and 1 report row at a time. So let's say you have a simplified COLUMN statement:

   column grpvar1 var2 var3 var4 var5;

 

  At the point in time when PROC REPORT is writing GRPVAR1 on the report row, it has NOT YET written var2, var3, var4 or var5. So that means the COMPUTE block for GRPVAR1 cannot refer to any item that appears to it's right on the COLUMN statement (which defines the report row). When PROC REPORT is writing GRPVAR1, it does not yet know what any of those other variable values are. But, at the point in time when PROC REPORT puts VAR5 on the report row, it knows what the value of all the previous items on the row are. But it only knows what it just put down. Once the row is written, PROC REPORT moves on to the next row and starts over.

 

  The good news is that when you have a COMPUTE block for VAR5, your CALL DEFINE statement in a COMPUTE block can change any item on the row.

 

  My advice is that you take a look at the attached screen shot and the way that I made the helper variables I needed. I did not use a "preprocessing" step like you did. I did everything in COMPUTE blocks. It is 6 of 1, etc, etc, which approach you choose. But the fact remains that however you do it, the COMPUTE block for MAKE will NOT be able to use the value in MAKE2. But inside a COMPUTE block for MAKE2, you could issue a URL for MAKE. I did not bother with a stored process, because 1) I don't have an image to play with right now and 2) I prefer to get things working using GOOGLE to make sure the "back half" of my URL is being built correctly and then once I get things working without a stored process, then I move everything to the world of stored processes.

 

  Hope this helps.

 

cynthia

 

create_dynamic_url_report.png

 

The full code:

ods _all_ close;
ods html file='c:\temp\cars_url.html' style=seaside;
proc report data=sashelp.cars spanrows;
   title '1) all helper columns displayed';
	where Make in("Audi" "BMW" "Mercedes-Benz");
	column make holdmake type holdtype useinurl driveTrain  weight length;
	define make / group f=$15.
       style(column)=Header;
    define holdmake / computed;
	define type / group f=$15.
	   style(column)=Header;
	define holdtype / computed;
    define useinurl / computed;
	define drivetrain / group f=$15.
	   style(column)=Header;
	define weight / mean f=comma9.2;
	define length / mean f=comma9.2;
	rbreak after / summarize;
	compute before make;
	  length xxx $15;
	  xxx = make;
	endcomp;
	compute before type;
	  length yyy $15;
	  yyy = type;
	endcomp;
	compute holdmake/character length=15;
	  holdmake = xxx;
	  if _break_ = ' ' then murl=catt('http://www.google.com/#q=',make);
	  else murl =  'http://www.google.com/#q=Average+MSRP+All+Cars';
	  call define('Make','url',murl);
	endcomp;
	compute holdtype/character length=15;
	  holdtype = yyy;
	  if _break_ = ' ' then turl=catt('http://www.google.com/#q=',holdmake,'+',holdtype);
	  call define('type','url',turl);
	endcomp;
	compute useinurl / character length =100;
	  ** show what will be used in the url;
	  useinurl = catt(holdmake,'+',holdtype);
	endcomp;
	compute drivetrain;
	  if _break_ = ' ' then durl=catt('http://www.google.com/#q=',holdmake,'+',holdtype,'+',drivetrain);
	  call define(_col_,'url',durl);
	endcomp;
    compute after;
	  Make = 'Total';
	  type = '~';
	  drivetrain = '~';
	  call define('Make','style','style=Header');
	  call define('type','style','style=Header ');
	  call define('drivetrain','style','style=Header ');
	endcomp;
run;

    
proc report data=sashelp.cars spanrows;
   title '2) helper columns hidden and report cleaned up';
 	where Make in("Audi" "BMW" "Mercedes-Benz");
	column make holdmake type holdtype driveTrain  weight length;
	define make / group f=$15.
       style(column)=Header;
    define holdmake / computed noprint;
	define type / group f=$15.
	   style(column)=Header;
	define holdtype / computed noprint;
	define drivetrain / group f=$15.
	   style(column)=Header;
	define weight / mean f=comma9.2;
	define length / mean f=comma9.2;
	rbreak after / summarize;
	compute before make;
	  length xxx $15;
	  xxx = make;
	endcomp;
	compute before type;
	  length yyy $15;
	  yyy = type;
	endcomp;
	compute holdmake/character length=15;
	  holdmake = xxx;
	  if _break_ = ' ' then murl=catt('http://www.google.com/#q=',make);
	  else murl =  'http://www.google.com/#q=Average+MSRP+All+Cars';
	  call define('Make','url',murl);
	endcomp;
	compute holdtype/character length=15;
	  holdtype = yyy;
	  if _break_ = ' ' then turl=catt('http://www.google.com/#q=',holdmake,'+',holdtype);
	  else turl = '_undef_';
	  call define('type','url',turl);
	endcomp;
	compute drivetrain;
	  if _break_ = ' ' then durl=catt('http://www.google.com/#q=',holdmake,'+',holdtype,'+',drivetrain);
	  else durl = '_undef_';
	  call define(_col_,'url',durl);
	endcomp;
    compute after;
	  Make = 'Total';
	  type = '~';
	  drivetrain = '~';
	  call define('Make','style','style=Header');
	  call define('type','style','style=Header{color=cxfaf3d4} ');
	  call define('drivetrain','style','style=Header{color=cxfaf3d4} ');
	endcomp;
run;

 ods html close;
 

 

New Contributor
Posts: 3

Re: STP ODS html drill down function with dependency

Hi Cynthia

This is perfect!

Thank you very much!

Phillip
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 288 views
  • 1 like
  • 2 in conversation