First, I know Vince states that absolute references aren't supported. After trying several ways to fool Excel into accepting them, I'm inclined to agree. That means my solution has to use relative references.
Let me describe the target state. I have now gotten there by two different methods. I'd be interested to find out other ways to get there.
The first sheet in the workbook has one or more threshold values and summary statistics. The other worksheets have data. The threshold value is used in conditional(s) on each row of the data sheets to generate something that is part of the summary statistics on the first sheet. In the examples below, I want to find the number of rows where the value exceeds the threshold. The threshold and count are on the first sheet. If the user changes the threshold, the count automatically updates. On the data sheet, I need to refer to the single threshold cell in the first sheet from every row in the data sheet.
My first idea was to use "style(head)={tagattr='formula:user_input!RC[3]'}, style(data)={tagattr='R[-1]C'}" in the VAR statement in PROC PRINT. Then the first row would refer to the threshold value, and every row below to the row above. This didn't work because the formula for the header wasn't getting put in the header row.
You can refine this idea, however. You can set the LABEL for the variable equal to the formula. Then the formula is placed in the header row:
DATA second;
LABEL testval = "='user_input'!R[3]C";
testval = ' ' ;
testform = ' ';
DO i = 1 TO 1000 ;
ranval = RANUNI(-3) * 5;
OUTPUT;
END;
DROP i ;
RUN;
ODS _ALL_ CLOSE ;
ODS TAGSETS.EXCELXP
FILE="toytestxml.xml"
STYLE=statistical
OPTIONS( sheet_name='user_input'
sheet_interval='none'
skip_space='0,0,0,0,0'
)
;
DATA uinput01;
LENGTH column1-column4 $ 100;
column1=' ' ;
column2='Please put in any positive number below' ;
column3=' ' ;
column4='No of Hits' ;
RUN;
DATA uinput02;
LENGTH column1-column4 $ 100;
column1 = 'Threshold:' ;
column2 = '3.5' ;
column3 = ' ' ;
column4 = ' ' ;
RUN;
PROC PRINT NOOBS LABEL DATA=uinput01 STYLE (HEADER)={tagattr='hidden:Yes'};
VAR column1 column2 column3 column4 ;
RUN;
PROC PRINT NOOBS LABEL DATA=uinput02 STYLE (HEADER)={tagattr='hidden:Yes'};
VAR column1 column2 column3 ;
VAR column4 / style(data)={tagattr='formula:COUNTIF(''data''!C[-1],"yes")'} ;
RUN;
ODS TAGSETS.EXCELXP
OPTIONS( sheet_interval='none'
sheet_name='data'
);
PROC PRINT NOOBS LABEL DATA=second;
VAR ranval ;
VAR testval / style(data)={tagattr='formula:R[-1]C'};
VAR testform / style(data)={tagattr='formula:IF(RC[-2]>RC[-1],"yes","no")'} ;
RUN;
ODS TAGSETS.EXCELXP
CLOSE ;
The other method is to run through the dataset, creating a relative reference in each row that does the math. In this case, i ~= _N_:
DATA second;
LENGTH testval $ 40 ;
LABEL testval = "='user_input'!R[3]C";
testval = "='user_input'" || '!R[' || COMPRESS(PUT(3-i,5.)) || ']C' ;
testform = ' ';
DO i = 1 TO 1000 ;
ranval = RANUNI(-3) * 5;
OUTPUT;
END;
DROP i ;
RUN;
[snip]
PROC PRINT NOOBS LABEL DATA=second;
VAR ranval ;
VAR testval ;
VAR testform / style(data)={tagattr='formula:IF(RC[-2]>RC[-1],"yes","no")'} ;
RUN;
I like the first method better, but the second works fine.
