topic Re: Capturing across variable value in ODS and Base Reporting

Capturing across variable value

Flip — Fri, 05 Mar 2010 18:38:25 GMT

I am adding hyperlinks to each cell in an excel spreadsheet using proc report.
Each link has parameters with row values of each group value. This works fine so far. I have Fiscal Year as an across variable. I would like to add this to the link so that the appropriate population may be found.

DEFINE CAMP / GROUP FORMAT= $6. WIDTH=6 SPACING=2 RIGHT "Campaign" order=internal;

DEFINE COMM / GROUP FORMAT= $14. WIDTH=14 SPACING=2 FLOW LEFT "CommCode" order=internal ;
DEFINE FYEAR / ACROSS FORMAT= $6. WIDTH=9 SPACING=2 RIGHT "Fiscal Year" descending;

compute before campidc /
style={background=#C5BE97};
line campidc $zcampnm.;
holdcamp = campidc;
endcomp;

compute after campidc ;
line " ";
endcomp;
compute commcode;
if commcode = 'z' then
call define(_ROW_,'STYLE','style={background=#B6DDE8}');
endcomp;
compute rowr_cnr;
href=cats("www.testurl.com?camp=", holdcamp, "?fy=", fyear, "?comp=", commcode);
call define(_col_, "URLBP", href);
endcomp;

Now here I cannot capture the value of FYEAR, in fact it puts 0 when it should be a string like FY2010.

Is there anyway I can capture a non-break value?

Re: Capturing across variable value

Cynthia_sas — Fri, 05 Mar 2010 19:56:15 GMT

Hi:
Actually, it would help to see ALL your PROC REPORT code, not just snippets. This is an instance where knowing what your COLUMN statement and which variables are COMPUTED and which are under the ACROSS variable would help.

However, your ACROSS item (FYEAR) is not available to you in the way you want. Consider this program and output:
[pre]
proc report data=sashelp.prdsale nowd
out=repout;
title 'Across Report';
column division product year,(actual predict);
define division / group;
define product / group;
define year / across;
define actual / sum;
define predict / sum;
run;

options nodate nonumber nocenter;
proc print data=repout;
title 'What PROC REPORT does with ACROSS columns';
run;
[/pre]

"Regular" PROC REPORT output:
[pre]
Year
1993 1994
Predicted Predicted
Division Product Actual Sales Sales Actual Sales Sales
CONSUMER BED $36,637.00 $32,353.00 $37,063.00 $37,838.00
CHAIR $34,311.00 $33,771.00 $35,703.00 $32,627.00
DESK $37,346.00 $35,222.00 $36,926.00 $40,592.00
SOFA $39,505.00 $38,809.00 $35,574.00 $34,811.00
TABLE $35,958.00 $37,638.00 $33,462.00 $33,924.00
EDUCATION BED $32,826.00 $38,570.00 $35,511.00 $29,106.00
CHAIR $38,709.00 $35,727.00 $39,557.00 $33,985.00
DESK $38,821.00 $33,605.00 $36,139.00 $36,776.00
SOFA $39,927.00 $37,669.00 $33,582.00 $29,162.00
TABLE $35,437.00 $36,847.00 $37,343.00 $37,263.00

[/pre]

PROC PRINT of output dataset
[pre]
What PROC REPORT does with ACROSS columns

Obs DIVISION PRODUCT _C3_ _C4_ _C5_ _C6_ _BREAK_

1 CONSUMER BED $36,637.00 $32,353.00 $37,063.00 $37,838.00
2 CONSUMER CHAIR $34,311.00 $33,771.00 $35,703.00 $32,627.00
3 CONSUMER DESK $37,346.00 $35,222.00 $36,926.00 $40,592.00
4 CONSUMER SOFA $39,505.00 $38,809.00 $35,574.00 $34,811.00
5 CONSUMER TABLE $35,958.00 $37,638.00 $33,462.00 $33,924.00
6 EDUCATION BED $32,826.00 $38,570.00 $35,511.00 $29,106.00
7 EDUCATION CHAIR $38,709.00 $35,727.00 $39,557.00 $33,985.00
8 EDUCATION DESK $38,821.00 $33,605.00 $36,139.00 $36,776.00
9 EDUCATION SOFA $39,927.00 $37,669.00 $33,582.00 $29,162.00
10 EDUCATION TABLE $35,437.00 $36,847.00 $37,343.00 $37,263.00

[/pre]

You can see how PROC REPORT deals with ACROSS variable items. It gives each ACROSS item an ABSOLUTE column name. So for example _c3_ is the ACTUAL value for 1993 and _c4_ is the PREDICT value for 1993. Similarly, _c5_ is the ACTUAL value for 1994 and _c6_ is the PREDICT value for 1994.

So, if you want to USE 1993 or 1994 in your code, then you do not have visibility of the value for YEAR anymore. You only know what the absolute column numbers are for each year. So you could do something like this:
[pre]
compute rowr_cnr;
href1993=cats("www.testurl.com?camp=", holdcamp, "?fy=", '1993', "?comp=", commcode);
href1994=cats("www.testurl.com?camp=", holdcamp, "?fy=", '1994', "?comp=", commcode);
call define('_c3_, "URLBP", href1993);
call define('_c5_, "URLBP", href1994);
endcomp;
[/pre]

Without seeing your full code, it's impossible to do more than hint at what you can do. I've seen some folks deal with needing a value for dynamic creation by "massaging" the data ahead of time or "flattening" the data and then building the URL dynamically.

cynthia

Re: Capturing across variable value

Flip — Fri, 05 Mar 2010 20:30:48 GMT

I think you answered my question. I will have to create the link for each group of the across variable. I was hoping there was some way within REPORT to grab this. I included the code below in case you see any further ways to handle this.

Thanks.

PROC REPORT DATA=WORK.dashrept LS=126 PS=35 SPLIT="/" NOCENTER nofs ;

COLUMN ( campidc campidc = camp commcode commcode = comm fyear,(
( "Contract"(rowr_cnr rowr_cnn rowr_cnt rowr_pct rowr_pts rowr_val))
( "Response"(rown_cnr rown_cnn rown_cnt rown_pct))));
DEFINE CAMPIDC / GROUP FORMAT= $12. WIDTH=9 SPACING=2 RIGHT "Campaign" order=internal descending noprint ;
DEFINE CAMP / GROUP FORMAT= $6. WIDTH=6 SPACING=2 RIGHT "Campaign" order=internal;
DEFINE COMMCODE / GROUP FORMAT= $zcommnm. WIDTH=20 SPACING=2 FLOW LEFT "Communication" order=internal ;
DEFINE COMM / GROUP FORMAT= $14. WIDTH=14 SPACING=2 FLOW LEFT "CommCode" order=internal ;
DEFINE FYEAR / ACROSS FORMAT= $6. WIDTH=9 SPACING=2 RIGHT "Fiscal Year" descending;
DEFINE rowr_cnr / SUM FORMAT= comma12. WIDTH=8 SPACING=2 RIGHT "RESP"
style(column)={borderleftcolor=#C5BE97 borderleftwidth=6 borderbottomcolor=#CCCCCC borderbottomwidth=1
bordertopcolor=#CCCCCC bordertopwidth=1 };
DEFINE rowr_cnn / SUM FORMAT= comma12. WIDTH=8 SPACING=2 RIGHT "NON-RESP" ;
DEFINE rowr_cnt / SUM FORMAT= comma12. WIDTH=8 SPACING=2 RIGHT "TOTAL" ;
DEFINE rowr_pct / SUM FORMAT= percent8.5 WIDTH=8 SPACING=2 RIGHT "RESP %" ;
DEFINE rowr_pts / SUM FORMAT= comma12. WIDTH=8 SPACING=2 RIGHT "POINTS" ;
DEFINE rowr_val / SUM FORMAT= dollar16.2 WIDTH=15 SPACING=2 RIGHT "REVENUE" ;
DEFINE rown_cnr / SUM FORMAT= comma12. WIDTH=8 SPACING=2 RIGHT "RESP"
style(column)={borderleftcolor=#B6DDE8 borderleftwidth=6 borderbottomcolor=#CCCCCC borderbottomwidth=1
bordertopcolor=#CCCCCC bordertopwidth=1 };
DEFINE rown_cnn / SUM FORMAT= comma12. WIDTH=8 SPACING=2 RIGHT "NON-RESP" ;
DEFINE rown_cnt / SUM FORMAT= comma12. WIDTH=8 SPACING=2 RIGHT "TOTAL" ;
DEFINE rown_pct / SUM FORMAT= percent8.5 WIDTH=8 SPACING=2 RIGHT "RESP %" ;

compute before campidc /
style={background=#C5BE97};
line campidc $zcampnm.;
holdcamp = campidc;
endcomp;

compute after campidc ;
line " ";
endcomp;
compute commcode;
if commcode = 'z' then
call define(_ROW_,'STYLE','style={background=#B6DDE8}');
endcomp;
compute rowr_cnr;
href=cats("www.testurl.com?camp=", holdcamp, "?fy=", fyear, "?comp=", commcode);
call define(_col_, "URLBP", href);
endcomp;

RUN;

Re: Capturing across variable value

Cynthia_sas — Fri, 05 Mar 2010 21:00:32 GMT

Hi:
I see that you have a LOT of nested variables under year. So you're right, you would have to hardcode the year value. If this was my report to do, I would be very tempted to flatten or pre-summarize the dataset and then build the URL so that absolute column numbers were not involved. Also remember, that even NOPRINT variables will get a column number, so you have to include them in your coding.

Also, do keep in mind when you use ODS, that some options, such as LS, PS, WIDTH, SPACING, FLOW are LISTING only options that are are simply ignored by ODS HTML, RTF and PDF. You don't indicate what your destiantion of choice is (no ODS statements) but given that you're coding a URL, I suspect it is ODS HTML.

Good luck!

cynthia