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
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;
Here's a clip of what I would like to see in my Excel file when it opens.
Thanks for your help!
WendyT
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?
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;
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.
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
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: