Hello All,
SAS 9.2 32bit
Is there a way to pass a column name dynamically within proc report? I am working on adding google analytics to track reporting usage. Based on the way our ODS .html pages that surface our reports are built, this is how it needs to be created or very similarly.
I need to pass the 'file' column within call define dynamically (into variable &string) so we know which report was viewed . I am having trouble getting the column name to act as a variable and resolve correctly. Any ideas?
Running on 9.2 32bit
options nocenter ;
DATA testing;
INPUT filename $ filetype $ file $;
DATALINES;
abc_rpt html hello
xyz_rpt pdf test
;
ods listing close;
ods html file="C:\Testing_links.html"
style=sasweb
;
title;
footnote;
options nodate nonumber;
proc report data=Testing nowd missing split='/'
style(report)={just=center}
;
column filename filetype file ;
define filename / order "Report Name" style=[just=left width=4in];
define filetype / display order=data "Format" style=[just=left width=.5in];
define file /noprint;
compute filetype;
if strip(filename) ne '' then do;
urlstring=strip(filename);
%let string = file;
CALL DEFINE(_COL_, 'URL', strip(urlstring) || ' "target="_blank' || %unquote(%nrbquote('" onClick="javascript: pageTracker._trackPageview(''/home/&string'' );')));
end;
endcomp;
run;
ods html close;
ods listing;
The issue is &string is not being resolved correctly.
Once the code above is ran, view the source of the html page.
Towards the bottom of the html's source page you will see the lines below. The setup of quotes/double quotes is very important and it should look like both examples below.
<a href="abc_rpt "target="_blank" onClick="javascript: pageTracker._trackPageview('/home/file' );">html</a>
<a href="xyz_rpt "target="_blank" onClick="javascript: pageTracker._trackPageview('/home/file' );">pdf</a>
The correct output would look like:
<a href="abc_rpt "target="_blank" onClick="javascript: pageTracker._trackPageview('/home/hello' );">html</a>
<a href="xyz_rpt "target="_blank" onClick="javascript: pageTracker._trackPageview('/home/test' );">pdf</a>
Any help is appreciated! Thanks!
Hi,
Some comments:
1) you probably don't need %let for 2 reasons;
--%LET works at compile time not execution, so your macro var is being set to the string 'file' and NOT, as you hope, to the current row's value for r the FILE variable.
--PROC REPORT will do what you want and use the value of the FILE var dynamically
2) BUT, in order for PROC REPORT to work, you have to specify the variables in the correct order on the COLUMN statement.
So, let's take this hypothetical example. SASHELP.CLASS has 5 variables: NAME, SEX, AGE, HEIGHT and WEIGHT
If I have this COLUMN statement:
COLUMN NAME AGE HEIGHT WEIGHT SEX;
and this silly COMPUTE block:
COMPUTE height;
if SEX = 'F' then height=height*1.1;
else if SEX='M" then height = height * 1.25;
ENDCOMP;
My COMPUTE block will fail. PROC REPORT builds the report row one column at a time, building the row from left to right. SO, when PROC REPORT is running the compute block for height, it has NO (ZERO, ZILCH) visibility of the value for SEX because SEX has not yet been placed on the report row. This is a fundamental principle of PROC REPORT.
There are good examples in the documentation of how this works and examples of how to build something like a URL dynamically.
cynthia
Hi,
Some comments:
1) you probably don't need %let for 2 reasons;
--%LET works at compile time not execution, so your macro var is being set to the string 'file' and NOT, as you hope, to the current row's value for r the FILE variable.
--PROC REPORT will do what you want and use the value of the FILE var dynamically
2) BUT, in order for PROC REPORT to work, you have to specify the variables in the correct order on the COLUMN statement.
So, let's take this hypothetical example. SASHELP.CLASS has 5 variables: NAME, SEX, AGE, HEIGHT and WEIGHT
If I have this COLUMN statement:
COLUMN NAME AGE HEIGHT WEIGHT SEX;
and this silly COMPUTE block:
COMPUTE height;
if SEX = 'F' then height=height*1.1;
else if SEX='M" then height = height * 1.25;
ENDCOMP;
My COMPUTE block will fail. PROC REPORT builds the report row one column at a time, building the row from left to right. SO, when PROC REPORT is running the compute block for height, it has NO (ZERO, ZILCH) visibility of the value for SEX because SEX has not yet been placed on the report row. This is a fundamental principle of PROC REPORT.
There are good examples in the documentation of how this works and examples of how to build something like a URL dynamically.
cynthia
Thanks Cynthia. It was literally as easy as changing the order in the Column statement.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.