The SAS Output Delivery System and reporting techniques

Need help with creating clickable Excel formulas/links

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 91
Accepted Solution

Need help with creating clickable Excel formulas/links

SAS folks-

 

We have SAS 64-bit SAS 9.4 on a Windows server (TS1M2 on X64_S08R2), and are using Excel 2007 on our PCs (soon to be 2013). 

 

I have overnight batches that make and export Excel files and reports, and they run the same code series with different input parameters, depending on project area.

 

I want to put two active links in the cells of the Excel file that deals with locations.  The first link goes to a detail table in an application on our intraweb, and the second to a little spotter map in the same internal application.  The value of the variable STN_ID identifies the location, and changes on every line of data.

 

I have tried to generate the links both with and without a pretty name in the cell.

 

from my PROC SQL SELECT statement:

 

CAT('=http://dasprod/station/station.do?actionType=viewStnLoc&stnId=',STN_ID)                                        AS STN_DETAILS ,

CAT('=HYPERLINK("http://dasprod/station/viewStationMap.jsp?sftwrId=null&stnId=',STN_ID,'","Map it")')   AS STN_MAPIT

 

and I use PROC EXPORT to send the file to Excel. When the Excel file is opened, the links appear as text.  However, when I click into a cell and hit Enter, the link activates, and a reclick will go to the destination.

 

I want to have the link already active when the file is opened.

 

In the documentation, it appears that ODS EXCEL is supposed to offer this by using formulas= in the options.  I tried all four options (yes, no, on, off) to no avail, and put the autofilter option in just for a test.  The autofilter works.

 

This particular case is links, but I will also be needing formulas to resolve in the next phase of the project.

 

Here is the example that I've been tinkering with using the sashelp.class dataset:

 

%LET OUTDIR=my_directory

 

ODS _ALL_ CLOSE ;

ODS EXCEL FILE="&OUTDIR.\ods_excel_output.xlsx"

options(formulas='no' autofilter='all');

 

DATA CLASS_ADD ; SET SASHELP.CLASS ;

FORMULA1 = '=A1' ;

FORMULA2 = 'http://support.sas.com' ;

FORMULA3 = "http://support.sas.com" ;

FORMULA4 = '=HYPERLINK("http://support.sas.com","SAS support")' ;

FORMULA5 = "=HYPERLINK('http://support.sas.com','SAS support')" ;

RUN ;

 

PROC PRINT DATA=CLASS_ADD(OBS=5);

RUN ;

 

ODS EXCEL CLOSE;

ODS LISTING ;

 

PROC EXPORT DATA=CLASS_ADD OUTFILE="&OUTDIR\proc_export_output.xlsx" DBMS=EXCEL2007 REPLACE LABEL ; NEWFILE=YES ; RUN ;

 

If it's possible, PROC EXPORT would be my preferred solution, because of it's speed and lack of formatting.  If ODS EXCEL works, how would I go about eliminating all formatting to make it appear like PROC EXPORT? 

 

Thanks so much for any help you can give me!

 

WendyT


Accepted Solutions
Solution
‎01-22-2016 10:10 AM
Super User
Posts: 9,662

Re: Need help with creating clickable Excel formulas/links

OK. These could give you a start .

 


data have ;
length station $15 linkage $70 ;
input station stn_id linkage ;
datalines ;
Station_ABC 44610 http://dasprod/station/station.do?actionType=viewStnLoc&stnId=44610
Location_123 100 http://dasprod/station/station.do?actionType=viewStnLoc&stnId=100
890_XYZ 99999 http://dasprod/station/station.do?actionType=viewStnLoc&stnId=99999
;
run ;

ODS EXCEL FILE="/folders/myfolders/ods_excel_output.xlsx";
 
PROC report DATA=have nowd;
columns station linkage stn_id ;
compute stn_id;
 call define(_col_,'url',linkage );
 call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;
RUN ;
 
ODS EXCEL CLOSE;

View solution in original post


All Replies
Frequent Contributor
Posts: 91

Re: Need help with creating clickable Excel formulas/links

Here's a clip of what I would like to see in my Excel file when it opens.

 

Thanks for your help!

 

WendyT

WANT_Excel_file.jpg


WANT_Excel_file.jpg
SAS Super FREQ
Posts: 8,740

Re: Need help with creating clickable Excel formulas/links

Hi:
When you invoke ODS EXCEL, try using STYLE=MINIMAL to remove all the colors and style specifications. The issue with using PROC EXPORT is that I do not believe you can do formulas or hyperlinks with PROC EXPORT -- that would be something to verify with Tech Support, though.

cynthia
Super User
Posts: 6,928

Re: Need help with creating clickable Excel formulas/links

Clickable links are a feature of HTML, so I'd advise to create a table with properly formulated <A tags in a .html file. Why use the execrable Excel for something that is better done with the right tool?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,662

Re: Need help with creating clickable Excel formulas/links

Maybe this is a start.

 

ODS EXCEL FILE="/folders/myfolders/ods_excel_output.xlsx";
 
DATA CLASS_ADD ; SET SASHELP.CLASS ;
FORMULA1 = 'sas support' ;
RUN ;
 
PROC report DATA=CLASS_ADD(OBS=5) nowd;
define FORMULA1/style(column)={url="www.sas.com" textdecoration=underline color=blue};
RUN ;
 
ODS EXCEL CLOSE;
Super User
Super User
Posts: 7,392

Re: Need help with creating clickable Excel formulas/links

Please don't code alkl in upper case, it makes reading harder.  In your code I see nothing coming from the SAS system as a reason to use that create the spreadsheet, i.e. you could just open Excel and post those links in, so not seeing the point here?  One of the ways I find easiest to get around this need to use Excel, is to export any data from SAS to CSV, and use Excel itself to open that CSV file and import the data.  The reason is that Excel is not a database/data entry/transport/etc. format.  Hence to use its functionality which is fundamentally different to any of the proper formats requires the use of certain Excel functions, hence do it in that software.  Yes, proc export can create a file with the data, but it doesn't fully utilise Excel, tagsets can be created, but they are interpreted and again don't use the full functionality.

Frequent Contributor
Posts: 91

Re: Need help with creating clickable Excel formulas/links

Thanks so much for all your responses!  I still need one last bit of help on changing the link.

 

Cynthia- style=minimal is perfect. 

 

KSharp - I've never used PROC REPORT before, and have been puzzling over the documentation. What you have posted works perfectly, but is backwards for what I'm actually trying to do.

 

Each line should actually have a slightly different link of

 

http://dasprod/station/station.do?actionType=viewStnLoc&stnId=

 

followed by the value of stn_id.

 

I've been tinkering, and can't seem to get URL to accept a variable name.  No quotes does not work, and with quotes (both single and double) gives the word preceded by the current output directory.  Starting off with http:// seems to be a requirement to make a link to the application.

 

I've also tried versions of the CAT() function, but no luck yet.  The closest I have gotten is to put the leading part of the link in single quotes, because the & in the link generates a macro variable warning.  The link is active, and opens the page, but does not go to the specific station.

 

It doesn't matter what text is displayed the cell, just that the link is active and clickable when Excel is opened.

 

A little bit of new code:

 

data have ;

length station $15 linkage $70 ;

input station stn_id linkage ;

datalines ;

Station_ABC 44610 http://dasprod/station/station.do?actionType=viewStnLoc&stnId=44610

Location_123 100 http://dasprod/station/station.do?actionType=viewStnLoc&stnId=100

890_XYZ 99999 http://dasprod/station/station.do?actionType=viewStnLoc&stnId=99999

;

run ;

 

ods _all_close ;

ods excel file="&temp_output./ods_excel_output.xlsx" style=minimal ;

 

proc report data=have(obs=5) nowd;

define stn_id/style(column)={url='http://dasprod/station/station.do?actionType=viewStnLoc&stnId=' textdecoration=underline color=blue} ;

run ;

 

ods excel close ;

 

KurtBremser & RW9 - I completely agree with you both that Excel is a poor choice for many reasons.  However, Excel is the only thing that my folks will use (sigh).  I will do lowercase from now on. 

 

Thanks to everyone for your help!

 

WendyT


view_excel_file.jpg
Solution
‎01-22-2016 10:10 AM
Super User
Posts: 9,662

Re: Need help with creating clickable Excel formulas/links

OK. These could give you a start .

 


data have ;
length station $15 linkage $70 ;
input station stn_id linkage ;
datalines ;
Station_ABC 44610 http://dasprod/station/station.do?actionType=viewStnLoc&stnId=44610
Location_123 100 http://dasprod/station/station.do?actionType=viewStnLoc&stnId=100
890_XYZ 99999 http://dasprod/station/station.do?actionType=viewStnLoc&stnId=99999
;
run ;

ODS EXCEL FILE="/folders/myfolders/ods_excel_output.xlsx";
 
PROC report DATA=have nowd;
columns station linkage stn_id ;
compute stn_id;
 call define(_col_,'url',linkage );
 call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;
RUN ;
 
ODS EXCEL CLOSE;
☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 727 views
  • 3 likes
  • 5 in conversation