SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Absolute Reference in Multisheet Tagset ExcelXP?

Reply
Occasional Contributor
Posts: 6

Absolute Reference in Multisheet Tagset ExcelXP?

I'm trying to create a multisheet workbook where there are two data tables.  On the first sheet the user can set a threshold.  On the other two tables, values in each row are compared with the threshold cell in the first sheet - if the value is higher, count it, otherwise ignore it.  I tried using TAGATTR in PROC PRINT.  That failed for me because it is apparently only resolves relative to the cell containing the RC formula.  After trying TAGATTR, I tried storing text of the function with the absolute reference:

  a34 = '=IF(RC[-3]>user_input!B4,1,0)' ;

And that failed.  In the XML file, it is correct:

<Cell ssSmiley FrustratedtyleID="data__l" ss:Formula="=IF(RC[-3]&gt;user_input!B2,1,0)" ss:Index="32"><Data ss:Type="String"></Data></Cell>

But after Excel 2007 parses it, there are extra quotation marks that prevent proper resolution - note the single quotes around the cell reference:

=IF(AC2>user_input!'B2',1,0)

I tried single and double quotes around the complete reference:

  a34 = '=IF(RC[-3]>''user_input!B4'',1,0)' ;

generated this XML

<Cell ssSmiley FrustratedtyleID="data__l" ss:Formula="=IF(RC[-3]&gt;'user_input!B2',1,0)" ss:Index="32"><Data ss:Type="String"></Data></Cell>

which throws an error and is replaced with nothing when opening in Excel.  Trying double quotes turns the reference into a character literal which is never true.

Any thoughts on how to get past this?  Is there a way to get it to leave the quotes off?

SAS Super FREQ
Posts: 8,642

Re: Absolute Reference in Multisheet Tagset ExcelXP?

Hi:

  This is really a question for Tech Support. If the quotes are being inserted by the TAGSET template incorrectly, then they will need to report it to the developers. Tech Support are the only folks who can verify that it is a problem or they might know a workaround for the issue.

cynthia

Occasional Contributor
Posts: 6

Re: Absolute Reference in Multisheet Tagset ExcelXP?

In the first example, it is Microsoft Excel that is adding inappropriate quotes in the middle and end of the reference.  My attempts to add quotes were to see if I could force that MS Excel behavior not to happen.

Part of the issue is I am trying to do something Microsoft doesn't actually support.  Vince states that Microsoft apparently didn't want to have to deal with both absolute and relative references and only supports relative references.  I've decided I'm going to have to live with a less-than-elegant workaround.

Grand Advisor
Posts: 9,699

Re: Absolute Reference in Multisheet Tagset ExcelXP?

You don't show your proc print code but having a similar issue you may need to need to specify Type:numeric for the variable as TypeSmiley Frustratedtring forces the quotes. I don't use the tagattr often enough to have looked up other Type that might be valid.

Occasional Contributor
Posts: 6

Re: Absolute Reference in Multisheet Tagset ExcelXP?

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.

Grand Advisor
Posts: 16,304

Re: Absolute Reference in Multisheet Tagset ExcelXP?

1. Do you have to use Tagsets? This is simple enough to do via VBA or DDE. 

2. Have you looked into creating a named range via Tagsets? If the cell was the entire named range, you can just refer to the name in the formula. Not sure about this one, but might be worth looking into.



Occasional Contributor
Posts: 6

Re: Absolute Reference in Multisheet Tagset ExcelXP?

Neither VBA nor DDE work in an AIX environment.

I don't know of any "range" tagset in the ODS ExcelXP tagset.  Could you fill me in?

Post a Question
Discussion Stats
  • 6 replies
  • 582 views
  • 3 likes
  • 4 in conversation