URL in CALL DEFINE

[Subbarao]

Hi,

Im unable to tag URL using CALL DEFINE in PROC REPORT? can somebody help on this. Code that i used here is

DATA TEMP;

SET SASHELP.AIR(OBS=10);

RUN;

ODS TAGSETS.EXCELXP PATH="ABC" FILE="REPORT.XLS" STYLE=PRINTER

OPTIONS(ABSOLUTE_COLUMN_WIDTH="11" GRIDLINES='NO' WRAPTEXT='NO' EMBEDDED_TITLES='YES' EMBEDDED_FOOTNOTES='YES');

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="TABONE");

PROC REPORT DATA=TEMP(KEEP=AIR);

COMPUTE AIR;/*DEFAULT ACTION FOR COMPUTE BLOCK IS TO SUM UP ALL ROWS FOR COLUMN 'AIR'*/

CALL DEFINE(_COL_,'URL',www.google.com);/*HYPERLINKING SUMMARY VALUE TO NEXT TAB*/

ENDCOMP;

RUN;

ODS TAGSETS.EXCELXP CLOSE;

When i go to excel and look at URL, i could see that it is going to abc\www.google.com , but i want it should go to www.google.com.

Thanks.

[Cynthia_sas]

Hi:

  Usually all 3 arguments to the CALL DEFINE are text strings, or expressions that resolve to text strings. Text strings or constants should be quoted. You do not show www.google.com as being quoted -- is that a typo? In addition, a complete syntactically correct URL would be: http://www.google.com. So, normally, if I was coding something, I would do:

CALL DEFINE(_COL_,'URL','http://www.google.com');

with the full URL, including HTTP in quotes as the 3rd argument.

  The other thing that's happening is that when you build a URL, the PATH= option generally gets "prepended" to the URL unless you specify something that tells ODS not to do that. The correct syntax is shown in the code below. The suboption you use on the ODS statement is (URL=NONE) and it goes next to the PATH= location.

  And, a clarification. It is NOT the compute block that is summarizing the AIR variable. When you have a report that ONLY contains a single numeric variable, the only thing that PROC REPORT can do is summarize the variable. Look at the output produced by report #3. There is NO Compute block in the PROC REPORT step for #3 and the summarization still happens.

  Compare that to report #2, where DATE has been added to the report row. Now, the summary happens for the formatted value of DATE (YEAR4.) and so you get a report row for every unique value of year in the subset.

cynthia

ODS TAGSETS.EXCELXP PATH="c:\temp" (url=none)

                    FILE="REPORT.xml" STYLE=PRINTER

    options(sheet_name="TABONE" embedded_titles='yes');

PROC REPORT DATA=sashelp.air(obs=10) nowd;

title '1) summarizing air';

  column air;

  COMPUTE AIR;

    CALL DEFINE(_COL_,'URL','http://www.google.com');

  endcomp;

RUN;

PROC REPORT DATA=sashelp.air(obs=100) nowd;

title '2) setting URL based on value for date';

  column date air;

  define date / group f=year4.;

  COMPUTE AIR;

    tempvar = year(date);

    if tempvar = 1950 then

      CALL DEFINE(_COL_,'URL','http://www.google.com');

    else if tempvar = 1952 then

      CALL DEFINE(_COL_,'URL','http://www.youtube.com');

    else if tempvar in(1955, 1956, 1957) then

      CALL DEFINE(_COL_,'URL','http://support.sas.com');

    else

      CALL DEFINE(_COL_,'URL','http://www.setgame.com');

  endcomp;

RUN;

proc report data=sashelp.air(obs=10);

  title '3) only 1 numeric variable and NO COMPUTE block';

  column air;

run;

ODS TAGSETS.EXCELXP CLOSE;

[Katie5]

Cynthia, this is very helpful code to assign the URL.

I have a question regarding the third argument for CALL DEFINE. yes, we can specify URL for each tempvar. What if i have >100 records to assign different URL? is there any easy way to specify for each records, instead of >100 if ... then...?

Another question is that if we can create the hyperlink for a .sas file (like /compoundname/protocolname/programs/tte_kmplot.sas)? i tried to put the folder name there, it works (for example: CALL DEFINE(_COL_,'URL','/compoundname/protocolname/programs');). But when using a sas file name(/compoundname/protocolname/programs/tte_kmplot.sas), it does not work.

Thanks a lot!