ods tagsets.excelxp, URLs and Column Names

[dcortell]

Hi all

With the below code I'm supposed to create a set of excel files, formatted, including the original columns headers, and with the column "url" working as link to click on within the excel.

However, While the excel files get properly created in terms of formats, I get two not wanted results:

a. It seems that the columns headers are not used. Instead the proc report show the column "labels". Not sure of the reason behind.

b. The column "Link2" which should include the link, show up as an empty one

Any help about understanding the reason of this behaviour more than welcome

Bests

D

%macro generate;

%do i=1 %to &N_markets;


DATA open_lead_%sysfunc(compress(&&&prefix&i.
%sysfunc(putn(%eval(%sysfunc(year(%sysfunc(today())))),z4.)))) ;

SET open_lead_%sysfunc(compress(&&&prefix&i.
%sysfunc(putn(%eval(%sysfunc(year(%sysfunc(today())))),z4.))));

Link2 =
('=HYPERLINK("http://google.com/leads/'||put(lead_id,best7.)||'/overview";
"Link")') ;

RUN ;

%end;

%mend generate;


%generate; 


/********************************************************************************/

%macro export;

%do i=1 %to &N_markets;


ods listing close;
ods tagsets.excelxp file="~/Not_started_leads_%sysfunc(compress(&&&prefix&i.
%sysfunc(putn(%eval(%sysfunc(year(%sysfunc(today())))),z4.)))).xls" 

style = sasweb options (sheet_interval = "none" sheet_name = "Pending_Leads" autofit_height='yes' autofilter="all");

	proc report data = open_lead_%sysfunc(compress(&&&prefix&i.
%sysfunc(putn(%eval(%sysfunc(year(%sysfunc(today())))),z4.))))
				      style(report)={background=white}
				      style(header)={background=lightblue}
				      style(column)={ width=1.5in background=white};
					  define Link2 / display;
					  compute interaction3;
					  call define(_col_,'url',Link2);
					  endcomp;

	col account_name lead_owner_name contact_full_name lead_id lead_created_date lead_type interaction1 interaction2 interaction3 Link2;

	run;

ods tagsets.excelxp close;
ods listing;

%end;

%mend export;

%export;

[ballardw]

---

@dcortell wrote:

Hi all

With the below code I'm supposed to create a set of excel files, formatted, including the original columns headers, and with the column "url" working as link to click on within the excel.

However, While the excel files get properly created in terms of formats, I get two not wanted results:

a. It seems that the columns headers are not used. Instead the proc report show the column "labels". Not sure of the reason behind.

---

Your Proc Report code does not show any "column headers" set. So the default is the variable label, or without a variable label the variable name. If you want a specific column header other than the default that should appear in the COL statement or a DEFINE statement for the variable, which you didn't supply for most of the variables. So you get defaults.

The documentation says that the URL option for Call Define only affects ODS HTML, HTML5, RTF, PDF, PowerPoint, EPUB destinations . Since you are sending the output to tagsets.excelxp, which is XML output then don't expect the link to work. I don't create xml with hyperlinks so I am not sure what you may need to do.

Note that it is very poor idea to lie to computers in general, and may be related to getting incorrect output. You generate tagsets.Excelxp (XML) output but name the file with an XLS (not even XLSX extension) which means that there are all sorts of potential disconnects between the expected binary file layout from XLS naming and the XML content. You may find things are at least more consistent when you name the file with an XML extension, then the program opening the file has a better chance of appropriate conversions.