ODS Excel - building within-document hyperlink using a numeric row variable

[Walternate]

Hi all,

I have a proc report step which outputs a documentation file with information about programs running within a specific wave (list of programs, input and output files produced, run date and time, etc.). 

Here is the step:

Everything is working except one thing. In the last segment, I am trying to build a within-spreadsheet hyperlink. The idea is that if someone clicks the value of program name (prgname), they will be taken to the appropriate row and column on the second tab for that program. The tab is named Program modifications (with a space). The column to go to will always be A. The row is determined by the prg_row variable. Prgname wil sometimes be missing on the initial tab, which I accounted for but I don't know if that will cause problems. Prg_row seems to be working fine and my code for the hyperlink works if I use A1 as the reference instead of A+prg_row value. I also made prg_row numeric when I built it. 

When I run this, it outputs with no errors and opens the Excel file, but when I click the program values, I get an error message. 

ods listing close;

    ods excel file = "&outfile."
        options(frozen_headers="yes"
                  frozen_rowheaders="yes"
                               sheet_name = "Program Information"
                                              flow = "tables");

ods escapechar='^';

proc report data=mydata nowd style(header)=[font_weight=bold font_face='Arial' font_size=4 just=center vjust=middle];

      col prgname purpose jobstart jobend jobdur infile outfile reccnt prg_row;

      /*SET FONT AND JUSTIFICATION*/
    define prgname / style(column)=[ font_face='Arial' font_size=2 just=r vjust=middle ];
    define purpose/ style(column)=[font_face='Arial' font_size=2 just=l vjust=middle ];
    define infile / style(column)=[ font_face='Arial' font_size=2 just=c vjust=middle];
    define outfile / style(column)=[ font_face='Arial' font_size=2 just=c vjust=middle];
   define jobstart / style(column)=[ font_face='Arial' font_size=2 just=c vjust=middle];
    define jobend / style(column)=[ font_face='Arial' font_size=2 just=c vjust=middle];
    define jobdur / style(column)=[ font_face='Arial' font_size=2 just=c vjust=middle];
    define reccnt / style(column)=[ font_face='Arial' font_size=2 just=c vjust=middle];

     define prg_row/display noprint;

     /*ADD HYPERLINK TO PROGRAM NAME TO LINK TO VALUE ON OTHER TAB*/

    compute prgname;
              if prgname ne ' ' then do;
                  urlstring2 = catt("#'Program modifications'!A", prg_row);
                  call define(_col_, 'URL', urlstring2);
             end;
            else urlstring2 = ' ';
        endcomp;
    run;

ods excel close; 

Here is some fake data for the relevant vars if that helps:

Have:

prgname prg_row  

a                     1

b                    5

c                     8 

d                   10

The ideal output would be an Excel file in which each program is listed (with the blank rows still intact between them)

and when you click a program value, it takes you to the second sheet to A, row determined by the prg_row value. 

Any help is much appreciated!

[Tom]

Why are you using CATT instead of CATS? Are there actually some spaces you do want inserted between the arguments?

[Tom · Accepted solution]

You listed PRGNAME before PRG_ROW on COLUMN statement. 

But then in the COMPUTE block for prgname you are referencing the value of prg_row, which has not been set yet at that point.

Either move PRG_ROW before PRGNAME in the COLUMN statement.

Or move the compute block to PRG_ROW and don't use _COL_ in the CALL DEFINE() statement.  Just hard code the column.  That should be easy since PRGNAME is the first column.