Help using Base SAS procedures

Proc Report: Capture Across Var for use in link

Reply
Contributor
Posts: 38

Proc Report: Capture Across Var for use in link

I am trying to build a link in a compute block and can't figure out how to capture the "Across" variable. Any suggestions?

NOTE: Variable Yr_Month is uninitialized.

proc report data=sum_rpt split='_' nowd out=rpt_data;
column Process_ID Yr_Month, rec_count;
define Process_ID / group 'Process ID';
define Yr_Month / across 'Processing Period';
define rec_count / '';
compute rec_count;
urlstr="do?_program=test_pgm"
||'&Process_ID='||catx('',Process_ID)
||'&Yr_Month='||catx('',Yr_Month)
||'&drilltarget=_SELF';
call define (_col_, 'URL', urlstr);
endcomp;
quit;

data sum_rpt;
infile datalines dlmstr=' ';
input PROCESS_ID Yr_Month $7. rec_count ;
datalines;
1 2010-11 742
1 2010-12 7114
1 2011-01 4685
1 2011-02 3617
2 2010-11 143
2 2010-12 533
2 2011-01 253
2 2011-02 106
3 2010-11 3848
4 2010-11 632
4 2010-12 10547
4 2011-01 13444
4 2011-02 14583
5 2010-11 1304
5 2010-12 1488
5 2011-01 1229
5 2011-02 1302
;
run;
SAS Super FREQ
Posts: 8,743

Re: Proc Report: Capture Across Var for use in link

Hi:
When PROC REPORT builds a report using ACROSS variables, it assigns absolute column numbers (such as _C2_, _C3_, _C4_) to each column that is underneath an ACROSS item. That means in your COMPUTE block that you cannot refer to the YR_MONTH variable by name, you would instead need to refer to the absolute column numbers. And, further, the value of _C2_, _C3_, etc would NOT be the value of YR_MONTH, but would, instead, be the value of REC_COUNT for that particular YR_MONTH. So, you'd have to use the absolute column number a different way.

This previous posting contains an example that's almost exactly what you want to do.
http://support.sas.com/forums/thread.jspa?messageID=33214膾
(although that posting has some typos in quoting, so a corrected example is shown below using your data and a simpler URL.)

For another explanation of using absolute column numbers, see:
http://support.sas.com/forums/thread.jspa?messageID=45079뀗

Another alternative would be to set the URL in the header, with a user-defined format (as shown in #2) -- this doesn't get ProcessID into the URL, though, I just offer it as a possible alternative approach.

Also, remember that if you are using this code to invoke a SAS Stored Process using SASStoredProcess/do? that not every client application may be able to receive HTML streaming results from invoking a URL. Just FYI.

cynthia
[pre]
ods html file='c:\temp\bldurl1.html' style=sasweb;
proc report data=sum_rpt split='_' nowd out=rpt_data;
title '1) Use absolute column numbers';
column Process_ID rec_count,Yr_Month;
define Process_ID / group 'Process ID';
define Yr_Month / across 'Processing Period';
define rec_count / ' ';
compute rec_count;
href201011 = 'http://www.setgame.com'||'?q=2010-11';
href201012 = 'http://www.setgame.com'||'?q=2010-12';
href201101 = 'http://www.setgame.com'||'?q=2011-01';
href201102 = 'http://www.setgame.com'||'?q=2011-02';
call define ('_c2_', 'URLBP', href201011);
call define ('_c3_', 'URLBP', href201012);
call define ('_c4_', 'URLBP', href201101);
call define ('_c5_', 'URLBP', href201102);
endcomp;
run;

proc print data=rpt_data label split='*';
title 'what is absolute column number for each YR_MONTH';
label _c2_ = '_c2_*2010-11'
_c3_ = '_c3_*2010-12'
_c4_ = '_c4_*2011-01'
_c5_ = '_c5_*2011-02';
run;
ods html close;

*** alternate: put URL on Header with format;
proc format;
value $yrurl
'2010-11' = 'http://www.google.com?q=2010-11'
'2010-12' = 'http://www.google.com?q=2010-12'
'2011-01' = 'http://www.google.com?q=2011-01'
'2011-02' = 'http://www.google.com?q=2011-02'
;
run;

ods html file='c:\temp\bldurl2.html' style=sasweb;
proc report data=sum_rpt split='_' nowd;
title '2) Put URL in Header only';
column Process_ID rec_count,Yr_Month;
define Process_ID / group 'Process ID';
define Yr_Month / across 'Processing Period'
style(header)={url=$yrurl.};
define rec_count / ' ';
run;
ods html close;
[/pre]
Contributor
Posts: 38

Re: Proc Report: Capture Across Var for use in link

Cynthia,

Thank you for your insights and explinations of the compute block. This solution gets me there with a defined set of input. Unfortunately I did not mention that the data would change and I appologize for that. So with that knowledge I built upon your solution along with some input from another SAS Paper that I found (Alison McMahill http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf) to come up with a solution to take data from any time frame and build the link dynamically.

The solution involves a little macro code and some other SQL code. First I create a dataset with the Yr_Months. Then you need to know the number of months that are in the data. Then I put the Yr_months into macro variables for later use. Once you have that then you can use a loop to reference the ABSOLUTE column reference as you described.

So here's another way to do it:

proc sql noprint;
create table mnths as
select distinct Yr_Month
from sum_rpt
order by Yr_Month
;
select count(1) into :Mnth_cnt
from mnths
;
select Yr_Month into :Mnth1-:Mnth%eval(&Mnth_cnt)
from mnths
;
quit;

%Macro Build;
proc report data=sum_rpt split='_' nowd out=rpt_data;
column Process_ID Yr_Month, rec_count;
define Process_ID / group 'Process ID';
define Yr_Month / across 'Processing Period';
define rec_count / '';
compute rec_count;
%do i=1 %to %eval(&Mnth_cnt);
urlstr="do?_program=test_pgm"
||'&Process_ID='||catx('',Process_ID)
||'&Yr_Month='||catx('',"&&Mnth&i")
||'&drilltarget=_SELF';
call define ("_c%eval(&i+1)_", 'URL', urlstr);
%end;
endcomp;
quit;
%Mend Build;
%Build

Another thing I noticed was in your reply you mention that not all clients might be able to receive streaming html results from a url... which leads me to the question: What's the prefered way to build summary reports that can drill down to the detail for consumption on the web (SAS Information Delivery Portal or SAS Web Report Studio) or maybe even SAS Add-In for MS?

Thanks again for all your help.
Jim
SAS Super FREQ
Posts: 8,743

Re: Proc Report: Capture Across Var for use in link

Hi:
Yes, that macro of Allison's is pretty cool.

About your other question about drilling down and the Platform. Most drill-down from a summary level down to a detail level is done mostly with OLAP cubes.

But, you can implement drill-down on the platform. For example, there are some examples of doing drill-down in the Stored Process Web Application (SPWA) samples. And, for information about creating drill-down URLs in the various client applications, you may want to refer to the Stored Process developer documentation. In addition, you may want to look in the Stored Process and Web Report Studio forums, where the topic comes up frequently.

It is useful, to me, to remember that the URL is a fairly specific construct. The anchor tag and the hyperlink it produces were designed by the W3C for use in a browser. So, if you were coding a stored process to be returned to a web browser, such as the Information Delivery Portal and the Stored Process Web Application (SPWA), then such anchor tag coding as _SELF for the href target might be OK; but that same anchor tag coding may not be an attribute that is respected by non-browser client applications. For example, what about Microsoft Word??? A client application like Word or Excel or PowerPoint may or may not allow drill-downs such as you envision.

And if drill-down control does work, where do you envision the drill-down happening -- for example -- a person has Word open, they run a stored process in Word and the stored process contains a drill-down hyperlink. When the person clicks on the hyperlink, would they get taken to a BROWSER window or would you envision the drilldown returning their results to Word????

As I said, I can envision this working if you were talking about a browser-based client, such as the Portal or the Stored Process Web App. The most common way to implement drill-down capability such as you describe is usually through OLAP cube drill-down. However, drill-down is possible with WRS -- searching on the forums and in the doc and opening a track with Tech Support will be your best resources.

cynthia
Ask a Question
Discussion stats
  • 3 replies
  • 378 views
  • 0 likes
  • 2 in conversation