topic Re: Hyperlinking sheets within SAS ODS Excel in ODS and Base Reporting

Hyperlinking sheets within SAS ODS Excel

LB — Thu, 14 Sep 2017 17:53:45 GMT

So I have code that will simply report out one page with hyperlinks and then the rest of the sheets will be generated by proc report via macro for all 21 medical centers.

So the first page is a proc report with all facilities and stats and then it connects to each facilities page-

i.e. ROS

However it is not working per se- I had it working in old code via ExcelXp tagset but not in the new ODS Excel destination-

This is what I have thus far-

However not sure if this is an issue with MS Office vs SAS.

Any assistance much appreciated.

~Lawrence

ods excel file="/apps/sas/datasets/data137/NCQOS/dev/xxx_sandbox/data/CAUTI/CAUTI.xlsx" style=Seaside options(sheet_name="STATISTICS" SHEET_INTERVAL= 'PAGE' FROZEN_ROWHEADERS='no'
GRIDLINES='ON' embedded_titles='yes' embedded_footnotes='yes');

proc report data=final_out headline headskip nowd spanrows
style (column)={background=white just=center color=black font =("arial",8.0pt)}
style (header)={background=white just=center color=black font =("arial",9.0pt)}
;
columns FACID pts_with_cath standard_ua foley_ua proper_ua_test_pct;

DEFINE FACID/"FACILITY/(Click /Facility/to go to/cases)"
style(column)={color=blue} /*format=$code.;*/;
DEFINE pts_with_cath/"Patients/With Catheters";
DEFINE standard_ua/"Patients/standard UA*";
DEFINE foley_ua/"Patients/Foley UA*";
DEFINE proper_ua_test_pct/"PCT With/Proper Test/Done" style={tagattr='format:0%'};

compute FACID ;
IF FACID ne 'TOT' then do;
urlstring = "#'FACID=" ||strip(facid)|| "'!A1";
call define(_col_, 'URL', urlstring);
end;
if FACID='TOT' then call define(_ROW_,'style',
'style={background=lightgray color=black}');
if FACID='TOT' then call define(_ROW_,'style',
'style={background=lightgray color=black}');
endcomp;
run; quit;

ods excel style=Seaside options( SHEET_INTERVAL= 'PAGE' FROZEN_ROWHEADERS='no' FROZEN_HEADERS= '4'
GRIDLINES='ON' sheet_name="ROS" embedded_titles='yes' embedded_footnotes='yes');

proc report data=UA_group (where=(standard_ua=1 and facid='ROS'))
style (column)={background=white just=center color=black font =("arial",8.0pt)}
style (header)={background=white just=center color=black font =("arial",9.0pt)};

columns SPECIALTY start_dtx end_dtx facid pat_mrn_id hosp_admsn_time hosp_disch_time start_dt end_dt PROC_CODE test_type PROV_NAME ;
define SPECIALTY/GROUP;
define start_dtx/noprint analysis;
define end_dtx/noprint analysis;
define start_dt/"START/CATHETER/TIME*";
define end_dt/"END/CATHETER/TIME*";
define Test_type /"Test";
define Proc_code/"Lab Code";
define PROV_NAME /"Provider";
define facid/noprint;

compute start_dt ;
if start_dtx.sum = . then call define(_COL_,'style','style={background=salmon color=black}');
endcomp;
run;

compute end_dt ;
if end_dtx.sum = . then call define(_COL_,'style','style={background=salmon color=black}');
endcomp;
run;

ODS _ALL_ Close;

ods listing;

Re: Hyperlinking sheets within SAS ODS Excel

PaigeMiller — Thu, 14 Sep 2017 18:25:29 GMT

SSince we don't know what values FACID takes, it's really impossible to say what is going on.

You need to provide us with a representative portion of the data, so we can actually run your code and figure out what the problem is.

Re: Hyperlinking sheets within SAS ODS Excel

Reeza — Thu, 14 Sep 2017 19:50:46 GMT

We don't need your data, we need a representative sample.

So if you can 'recode' your exmaple to work with SASHELP.CARS maybe and put each make on a different tab?

I would recommend looking at how Excel creates a link via formulas, use proc report to build a table - the table of contents - that links to the other sheets and then display that.

Similar to the idea here maybe:

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-EXCEL-creating-hyperlinks-improperly/td-p/242811

Re: Hyperlinking sheets within SAS ODS Excel

LB — Thu, 14 Sep 2017 20:01:36 GMT

True enough, unfortunately due to sensitive medical information-even offering a representaive data would require a lot of work.

However I did manage to resolve the issue on my own.

Lawrence

Re: Hyperlinking sheets within SAS ODS Excel

LB — Thu, 14 Sep 2017 20:06:33 GMT

Reeza;

Thanks. Going back and reviewing the code I realized it was a programming oversight on my end-

The pink sections didn't match between the links as the compute facid would equal FACID=ROS!A1

but the sheet was simply 'ROS'

A byproduct of switching methods.

compute FACID ;
IF FACID ne 'TOT' then do;
urlstring = "#'FACID=" ||strip(facid)|| "'!A1";
call define(_col_, 'URL', urlstring);
end;

ods excel style=Seaside options( SHEET_INTERVAL= 'PAGE' FROZEN_ROWHEADERS='no' FROZEN_HEADERS= '4'
GRIDLINES='ON' sheet_name="ROS" embedded_titles='yes' embedded_footnotes='yes');

Re: Hyperlinking sheets within SAS ODS Excel

ballardw — Thu, 14 Sep 2017 20:07:23 GMT

You might post the code that was working for tagsets.Excelxp for comparison.

Also please post code into a codebox opened with the forum icons {i} or the "SAS Run" to preserve code formatting. The message windows here reformat text and make code hard to read with all the indents are removed.