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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
Phillipwedel
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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;
 

 

Phillipwedel
Calcite | Level 5
Hi Cynthia

This is perfect!

Thank you very much!

Phillip

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 1038 views
  • 1 like
  • 2 in conversation