topic Re: Proc Report Colum Reference in SAS Programming

Proc Report Colum Reference

VCucu — Thu, 29 Apr 2021 10:44:09 GMT

Hello,

I have the following code for a proc report:

title1 "Table1"; 
proc report data=r12 nowd list headline headskip spacing=2 split='~' contents='';
column  (num_mandant value dax_Generation, (metric_sum Dev1 dev2 dev3 dev4));
define num_mandant / group 'Mandant Code';
define value / group 'Basel COREP Exposure Class';
define dax_generation / across 'Time Period';
define metric_sum / sum  f=comma20. 'Exposure Book Value Amount';
define dev1 / computed nozero f=percent9.1 style=[backgroundcolor=color.]  'vs M-1.~(%)';
define dev2 / computed nozero format=comma20.1 'vs. M-1~(EUR mn.)';
define dev3 / computed nozero f=percent9.1 style=[backgroundcolor=color.]  'vs. M-2~(%)';
define dev4 / computed nozero format=comma20.1 'vs. M-2~(EUR mn.)';
compute dev1;
_c14_=_c13_/_c8_-1;
endcomp;
compute dev2;
_c15_=(_c13_-_c8_)/1000000;
endcomp;
compute dev3;
_c16_=_c13_/_c3_-1;
endcomp;
compute dev4;
_c17_=(_c13_-_c3_)/1000000;
endcomp;
compute value;
call define(_col_,'url',"M:\OE0979\SPR\reporta_&md._analysis12_"||trim(value)||".html"); 
endcomp;
rbreak after / summarize style=[font_weight=bold];
run;

This basically produces a hyperlink in the 2nd column as seen below.

I would like to produce the hyperlink only if _col13_>10.000.000 and abs(_col14_)>0.1

I tried to use the following code but it stil creates hyperlinks for all values:

compute value;
if _C13_>10000000 abs(_c14_)>0.1 then call define(_col_,'url',"M:\OE0979\SPR\reporta_&md._analysis12_"||trim(value)||".html"); 
endcomp;

What should i change ?

Re: Proc Report Colum Reference

PaigeMiller — Thu, 29 Apr 2021 10:49:08 GMT

Start by using proper syntax in your IF

if _C13_>10000000 and abs(_c14_)>0.1 then call define(_col_,'url',"M:\OE0979\SPR\reporta_&md._analysis12_"||trim(value)||".html");

Re: Proc Report Colum Reference

VCucu — Thu, 29 Apr 2021 10:51:11 GMT

i dont get any error using the syntax above. 🙂 what do you mean ?

Re: Proc Report Colum Reference

Kurt_Bremser — Thu, 29 Apr 2021 11:11:44 GMT

I seriously doubt that. Code in a COMPUTE has to be valid DATA step code:

 73         data _null_;
 74         _c13_ = 1000000000000;
 75         _c14_ = 1;
 76         if _C13_>10000000 abs(_c14_)>0.1 then put "Yes";
                              ___
                              22
 ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, 
               GT, LE, LT, MAX, MIN, NE, NG, NL, OR, ^=, |, ||, ~=.

There must be and AND or OR where the ERROR indicator points to.

Re: Proc Report Colum Reference

PaigeMiller — Thu, 29 Apr 2021 12:28:52 GMT

@VCucu wrote:
i dont get any error using the syntax above. 🙂 what do you mean ?

I mean use the EXACT code I provided.

But, if you don't get a syntax error and @Kurt_Bremser does using the code you provided, then it would seem that you are not running the exact code you showed us. That makes it difficult (if not impossible) for us to help.

Re: Proc Report Colum Reference

VCucu — Fri, 30 Apr 2021 08:35:24 GMT

Hi guys,

Sorry for all that. Now i get it, i will always include a small code example. Please see below example code.

DATA report;
 INPUT mandant generation metric$ customer$ sum;
CARDS;
196 202104 sales AAA 100
196 202104 sales BBB 95
196 202104 sales CCC 80
196 202103 sales AAA 90
196 202103 sales BBB 90
196 202103 sales CCC 90
196 202102 sales AAA 90
196 202102 sales BBB 90
196 202102 sales CCC 90
;
RUN; 

proc format;
   value color -10-<-0.1='red' -0.1-<-0.05='yellow' -0.05-<0.05='green' 0.05-<0.1='yellow' 0.1-<10='red';
           

proc report data=report nowd list headline headskip spacing=2 split='~' contents='';
column  (mandant customer Generation, (sum Dev1 dev2 dev3 dev4));
define mandant / group 'Mandant Code';
define customer / group 'Customer';
define generation / across 'Time Period';
define sum / sum  f=comma20. 'Amount';
define dev1 / computed nozero f=percent9.1 style=[backgroundcolor=color.]  'vs M-1.~(%)';
define dev2 / computed nozero format=comma20.1 'vs. M-1~ (Amount)';
define dev3 / computed nozero f=percent9.1 style=[backgroundcolor=color.]  'vs. M-2~(%)';
define dev4 / computed nozero format=comma20.1 'vs. M-2~ (Amount)';
compute dev1;
_c14_=_c13_/_c8_-1;
endcomp;
compute dev2;
_c15_=(_c13_-_c8_);
endcomp;
compute dev3;
_c16_=_c13_/_c3_-1;
endcomp;
compute dev4;
_c17_=(_c13_-_c3_);
endcomp;
compute customer;
if abs(_c15_)>5 then call define(_col_,'url',"M:\OE0979\SPR\test_"||trim(customer)||".html"); 
endcomp;
rbreak after / summarize style=[font_weight=bold];
run;

I would like same as above to create this hyperlink only if for example abs(_c15_)>5 . If i use the code above it does not work... all the hyperlinks are dead.

What am I doing wrong ? 😛

Thank you,