Proc Report

[Spintu]

Hi Team,

I have to generate a report with hyperlink based on Year_Month column value. The hyperlink should be act on as per given highlighted value. I am able to generate the link with my existing .xls file. However the file generating for only one month value 2018M08. Dynamically it is not moving to next file based on Year_Month column value.

Could you please assist me?

data _null_;
	set DATE_chk;
	call symput('date'||left(_n_),trim(YEARMON));
	call symput('date_c',_n_);
run;

FILENAME fileout "&path/Test-write-from-PC_Summary.html";
options number pageno=1 center nodate orientation=landscape;
ods listing close;
ods html file=fileout style=styles.printer;
proc report data=FINAL  nowd headline headskip split = '|';
  column YEARMON RCOMMITTEE TOT_SC TOT_PB TOT_PCT; 
  define YEARMON		/ "Year|Month"	group center width=20
						  order=data style=[foreground=Blue font_size=2];
  define RCOMMITTEE     / "Regional Committee"  center width = 30; 
  define TOT_SC     	/ "Total Number of Applications" SUM center width =  5;
  define TOT_PB     	/ "Number Shipped to QM"	center width =  5;
  define TOT_PCT		/ "Exceptions" 	center format=percent7.2;
  compute RCOMMITTEE; if RCOMMITTEE = 'ZZTOTAL' then 
	DO;
	  RCOMMITTEE =  "** Total **";
	  call define (2,"style","style=[foreground=Blue font_size=2]");  
	END; 
  endcomp;
	 compute TOT_SC;   
		%do i=1 %to &date_c;
		call define(_col_,"URL","test-m_&&date&i...xls");
		%end; 	   
	 endcomp; 
 run;
title;
run;
ods html close;

[Cynthia_sas]

Hi:
  There must be more to the process than you're showing. I can see where &date macro variable gets a value. I can't see where &path gets a value. Your macro %DO loop should be resolved at macro compile time. It is not running dynamically for every row of data on the report. So one reason your %DO loop might not be working is that you are using the macro %DO loop incorrectly. Consider this test with SASHELP.CLASS:

It doesn't matter whether I have the %DO in a DATA step or inside PROC REPORT, the %DO won't work. The %DO loop cannot be executed in open code. So there must be more to your program or you are using the Macro %DO in the wrong way to do what you want.

  And it's not clear to me what you want to do -- it looks like you have 10 observations and you want to put a hyperlink on the TOT_SC column? But you show more than one column highlighted like a hyperlink in your screen shot. So are you trying to put a hyperlink on EVERY row for TOT_SC or only on the ZZTOTAL row? Is every row getting the same hyperlink based on YEARMON or is every row getting a different hyperlink?

  What about your data -- I see that you are using DATE_CHK and FINAL? What do those files look like?

  But, without any test data, it is impossible to test your code. It does look like you might have "pre-summarized" the data based on the IF statement for ZZTOTAL, but I don't see that in the data, so I'm not entirely sure what you're trying to accomplish. Did you start with a working program without any macro coding? Is this entire program in a macro definition that you call?

  As an additional comment, options like headline, headskip. orientation and width= are irrelevant to ODS HTML and are ignored since they are LISTING only options and you have turned off the LISTING destination. In addition, the system option NUMBER is ignored by ODS for the HTML destination. NUMBER and DATE options are only used by ODS RTF and ODS PDF destinations because those are "paged" destinations.

  I'm also curious -- this desired output looks like Excel output -- not HTML output, so I'm confused why you're showing the desired report in Excel. Are you getting any HTML output from PROC REPORT? I see that you are using YEARMON as a GROUP item on the report, but you do NOT have GROUP for the RCOMMITTEE variable. Are you seeing any messages in the LOG related to the usage of RCOMMITTEE being the default of DISPLAY or the usage of YEARMON changing to ORDER???

  In order to do anything with your program, people have to recreate your data file and then, in order to test correctly, they also need to make some XLS files that are used for the URL drill down. Can you show what you expect the URL to look like, without reference to Macro variables for each of the YEARMON values on your report? What would the XLS files look like? Do you have a working hyperlink that drills down from an HTML file to an XLS file? If so, what is THAT URL?

Cynthia

[Spintu]

Thank you for your response. I'm getting it now. I have resolved what I am expecting. Thank you