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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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

wreneau
Calcite | Level 5

Thanks Cynthia. It was literally as easy as changing the order in the Column statement.

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
  • 2 replies
  • 1559 views
  • 2 likes
  • 2 in conversation