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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

7 REPLIES 7
WendyT
Pyrite | Level 9

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
Cynthia_sas
SAS Super FREQ
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
Kurt_Bremser
Super User

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?

Ksharp
Super User

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

WendyT
Pyrite | Level 9

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

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;

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
  • 7 replies
  • 3166 views
  • 3 likes
  • 5 in conversation