topic Re: proc report URL help in SAS Procedures

proc report URL help

tony_mccray — Wed, 25 Mar 2009 19:39:29 GMT

I've put together the proc report code shown below. As you will see I'm attempting to make several values listed in the report into links via compute blocks. The urls include two values from the data set being used in the report: site and logop. This works great in the first row of the report. In subsequent rows the links get set up but the logop value is missing from the url every time! Both the site and logop variables appear in every row of the data set being used. Site shows up in the every url as expected. Any ideas how to fix this?

proc report data = report_data_fam ( where = ( day >= TODAY() - 7 and src eq &src ) )
nowd
split = ' '
style( header ) = { background=CX5382A1
foreground=white
font_size = 3 }
style( column ) = { font_size = 3 };
column src logop platform site day,( units_tested units_failed yield ) dummy;
define src / ' ' group;
define logop / ' ' group;
define platform / ' ' group;
define site / ' ' group;
define day / ' ' across format = MMDDYY. descending;
define units_tested / 'T' analysis;
define units_failed / 'F' analysis;
define yield / 'Y' analysis format = percent9. center;
define dummy / computed noprint;

compute site;
call define ( '_c1_', 'style', 'style=[background=CXE76F00 font_weight=bold foreground=white]' );
call define ( '_c2_', 'style', 'style=[background=CXE76F00 font_weight=bold foreground=white]' );
call define ( '_c3_', 'style', 'style=[background=CXE76F00 font_weight=bold foreground=white]' );
call define ( '_c4_', 'style', 'style=[background=CXE76F00 font_weight=bold foreground=white]' );
endcomp;

compute units_tested;
%if &version = Dev %then %do;
urlstring = 'http://ws-uprd-01/~am153274/SAS/Dev/GlobalQuality/YieldMetrics/'
||trim(site)||'_'||trim(logop)
||'_ProductYieldData.html#'
||trim(platform);
%end;
%else %do;
urlstring = 'http://ws-uprd-01/~am153274/SAS/GlobalQuality/YieldMetrics/'
||trim(site)||'_'||trim(logop)
||'_ProductYieldData.html#'
||trim(platform);
%end;
call define ( _col_, 'url', urlstring );
endcomp;

compute dummy;
cnt+1;
if mod( cnt, 2 ) then
call define( _row_, 'style', 'style=[background=CXFFFFFF font_weight=bold ]' );
else
call define ( _row_, 'style', 'style=[background=CXD3D3D3 font_weight=bold ]' );
endcomp;
run;

Re: proc report URL help

Cynthia_sas — Thu, 26 Mar 2009 03:49:11 GMT

Hi:
Consider the following output:
[pre]
Region showreg Product Total Sales
Asia Asia Boot 62,708
Asia Men's Casual 11,754
Asia Men's Dress 119,366
Asia Sandal 8,208
Asia Slipper 152,032
Asia Sport Shoe 2,092
Asia Women's Casual 25,837
Asia Women's Dress 78,234
Asia Asia 460,231

Canada Canada Boot 385,613
Canada Men's Casual 441,903
Canada Men's Dress 920,101
Canada Sandal 14,798
Canada Slipper 952,751
Canada Sport Shoe 140,389
Canada Women's Casual 410,807
Canada Women's Dress 989,350
Canada Canada 4,255,712

Pacific Pacific Boot 123,575
Pacific Men's Casual 662,368
Pacific Men's Dress 426,191
Pacific Sandal 48,424
Pacific Slipper 390,740
Pacific Sport Shoe 26,169
Pacific Women's Casual 219,886
Pacific Women's Dress 399,441
Pacific Pacific 2,296,794

[/pre]

The above report shows how PROC REPORT suppresses the duplicate or repetitive values for a GROUP variable (REGION) in the first column on the report. By default, only the first occurence of a group and any summary line will contain the GROUP variable value. That means the value for REGION is blank or suppressed on ALL the other report rows for the group. This is a little favor that PROC REPORT does for you, which in your case, is giving you what you want for the first report row, but NOT what you want on the subsequent report rows.

So, you have to "grab" and save the region value at the beginning of the group. You can do this in a compute block -- that's how I made the SHOWREG column. It is likely that a technique like this would allow you to build your url string in a compute block.

The code that created the above output is shown below.

cynthia
[pre]
proc sort data=sashelp.shoes out=shoes;
by region product;
where region in ('Asia','Canada', 'Pacific');
run;

proc report data=shoes nowd;
column region showreg product sales;
define region /group f=$8.;
define product / group f=$15.;
define showreg / computed f=$8.;
define sales / sum f=comma12.;
break after region / summarize skip;
compute before region ;
** grab the region on the first of the group;
holdreg = region;
endcomp;
compute showreg / character length=15;
showreg = holdreg;
endcomp;
run;
[/pre]

Re: proc report URL help

tony_mccray — Thu, 26 Mar 2009 17:44:40 GMT

Thanks Cynthia! This pretty much worked. The weird thing is if I add in a compute block to hold the value of the logop variable and try to use it in the urlstring it still doesn't work. When I use a computed item similar to your 'showreg' example the logop both prints and appears correctly in the urlstring. So I ended up using a computed item, show_logop, with a noprint. That gets me the result I wanted! Revised code is below, in case it might help others:

proc report data = report_data_fam ( where = ( day >= TODAY() - 7 and src eq &src ) )
nowd
split = ' '
style( header ) = { background=CX5382A1
foreground=white
font_size = 3 }
style( column ) = { font_size = 3 };
column src logop show_logop platform site day,( units_tested units_failed yield ) dummy;
define src / ' ' group;
define logop / ' ' group;
define show_logop / ' ' computed noprint;
define platform / ' ' group;
define site / ' ' group;
define day / ' ' across format = MMDDYY. descending;
define units_tested / 'T' analysis;
define units_failed / 'F' analysis;
define yield / 'Y' analysis format = percent9. center;
define dummy / computed noprint;

compute site;
call define ( '_c1_', 'style', 'style=[background=CXE76F00 font_weight=bold foreground=white]' );
call define ( '_c2_', 'style', 'style=[background=CXE76F00 font_weight=bold foreground=white]' );
call define ( '_c3_', 'style', 'style=[background=CXE76F00 font_weight=bold foreground=white]' );
call define ( '_c4_', 'style', 'style=[background=CXE76F00 font_weight=bold foreground=white]' );
endcomp;

/* Store logop for constructing links. */
compute before logop;
this_logop = logop;
endcomp;

compute show_logop / character length=3;
show_logop = this_logop;
endcomp;

compute units_tested;
%if &version = dev %then %do;
urlstring = 'http://ws-uprd-01.west/~am153274/SAS/Dev/GlobalQuality/YieldMetrics/'
||trim(site)||'_'||trim(this_logop)
||'_ProductYieldData.html#'
||trim(platform);
%end;
%else %do;
urlstring = 'http://ws-uprd-01.west/~am153274/SAS/GlobalQuality/YieldMetrics/'
||trim(site)||'_'||trim(this_logop)
||'_ProductYieldData.html#'
||trim(platform);
%end;
call define ( _col_, 'url', urlstring );
endcomp;

%if &cnt eq 2 %then %do;
compute units_failed;
%if &version = dev %then %do;
urlstring = 'http://ws-uprd-01/~am153274/SAS/Dev/GlobalQuality/YieldMetrics/'
||trim(site)||'_'||trim(this_logop)
||'_HW_Fails.html#'
||trim(platform);
%end;
%else %do;
urlstring = 'http://ws-uprd-01/~am153274/SAS/GlobalQuality/YieldMetrics/'
||trim(site)||'_'||trim(this_logop)
||'_HW_Fails.html#'
||trim(platform);
%end;
call define ( _col_, 'url', urlstring );
endcomp;
%end;

compute dummy;
cnt+1;
if mod( cnt, 2 ) then
call define( _row_, 'style', 'style=[background=CXFFFFFF font_weight=bold ]' );
else
call define ( _row_, 'style', 'style=[background=CXD3D3D3 font_weight=bold ]' );
endcomp;
run;
quit;