BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LB
Quartz | Level 8 LB
Quartz | Level 8

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LB
Quartz | Level 8 LB
Quartz | Level 8

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

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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
Reeza
Super User

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

 

 

 

 

LB
Quartz | Level 8 LB
Quartz | Level 8

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

LB
Quartz | Level 8 LB
Quartz | Level 8

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

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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