The SAS Output Delivery System and reporting techniques

Hyperlinking sheets within SAS ODS Excel

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 150
Accepted Solution

Hyperlinking sheets within SAS ODS Excel

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;

 

 


Accepted Solutions
Solution
‎09-14-2017 04:06 PM
Regular Contributor
Regular Contributor
Posts: 150

Re: Hyperlinking sheets within SAS ODS Excel

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');

View solution in original post


All Replies
Respected Advisor
Posts: 2,054

Re: Hyperlinking sheets within SAS ODS Excel

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.

--
Paige Miller
Super User
Posts: 20,731

Re: Hyperlinking sheets within SAS ODS Excel

Posted in reply to PaigeMiller

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/...

 

 

 

 

Solution
‎09-14-2017 04:06 PM
Regular Contributor
Regular Contributor
Posts: 150

Re: Hyperlinking sheets within SAS ODS Excel

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');

Regular Contributor
Regular Contributor
Posts: 150

Re: Hyperlinking sheets within SAS ODS Excel

Posted in reply to PaigeMiller

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

Super User
Posts: 11,810

Re: Hyperlinking sheets within SAS ODS Excel

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 442 views
  • 2 likes
  • 4 in conversation