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

Hi,

Not sure if this is possible, but I am exporting a cross tabular freq table to an HTML document. I am wondering if there is a way to show a line listings of the observations behind each number when the user clicks on that particular number.

eg.

Freq Table:

                    CAT 1     CAT 2     TOTAL

QUEUE A     10               20          30

QUEUE B     5                 15          20

TOTAL          15               25          50

So when a user clicks on 10 in th HTML document, display the line listing of the 10 observations that make up that number. Also would like that functionality when user clicks on the totals.

I am using SAS92 and EG43

Thanks for any help

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi: Style={URL=} will not work with PROC FREQ, however. So the approach would be to generate the output with FREQ and/or TABULATE or REPORT (I'd probably pick PROC REPORT) and then do the drill downs using the CALL DEFINE in PROC REPORT. But you also have to make the "drill-down" files. For example in the above 2x2 table with a total in the column and a total at the end of the table, you have 9 cells that would each need to have a drill-down file, so there are a few different ways to plan for the names of the drill-down files (assuming they will be static):

               CAT 1     CAT 2           TOTAL

QUEUE A     qA_c1.html   qA_c2.html      qA_tot.html

QUEUE B     qB_c1.html   qB_c2.html      qB_tot.html

  

TOTAL       c1_tot.html  c2_tot.html     cat_tot.html

OR, do you really need 9 separate drill down files? You could show 1 drill-down page with for each row

               CAT 1       CAT 2           TOTAL

QUEUE A     qA_tot.html   qA_tot.html      qA_tot.html

QUEUE B     qB_tot.html   qB_tot.html      qB_tot.html

  

TOTAL       cat_tot.html  cat_tot.html      cat_tot.html

OR you could show 1 drill down page with for each column:

               CAT 1       CAT 2           TOTAL

QUEUE A     c1_tot.html   c2_tot.html      cat_tot.html

QUEUE B     c1_tot.html   c2_tot.html      cat_tot.html

    

TOTAL       c1_tot.html   c2_tot.html      cat_tot.html

  

:So, there are examples of doing drill-down, but it will not be easy to do with PROC FREQ. You will have to look to another procedure, like PROC REPORT and you will have to make ALL the files -- the "main" file and then all the drill-down files. And then you have to use the style={url=} syntax to build the link. Here's a simpler example, where the link is on the ROW variable. And I made separate files for just the males and the females and then nothing for the Total. But it should give you the idea of how much more complicated it would be to attach a link to the number in the cell.

Cynthia


** 1) First make the drill-down files, so you know the names;
ods html path='c:\temp' (url=none)
             file='Females.html' style=sasweb;
proc print data=sashelp.class noobs n;
title 'Data on Women';
title2 link='file_with_url.html' 'Go Back To Summary';
footnote link='Males.html' 'Detail Report for Men';
where sex = 'F';
var name age height;
run;
ods html close;

   

ods html path='c:\temp' (url=none)
             file='Males.html' style=sasweb;
proc print data=sashelp.class noobs n;
title 'Data on Men';
title2 link='file_with_url.html' 'Go Back To Summary';
footnote link='Females.html' 'Detail Report for Women';
where sex = 'M';
var name age height;
run;
ods html close;

    

** 2) Create a user-defined format with the names of the files;
proc format;
  value $link 'M' = 'Males.html'
              'F' = 'Females.html';
run;
ods listing close;


** 3) Now use the format in a URL= style override.;
ods html path='c:\temp' (url=none)
             file='file_with_url.html'
             style=sasweb;

 
proc report data=sashelp.class nowd;
title 'with PROC REPORT';
title2 'click on a link to see the detail data';
footnote ;
column sex n age height;
define sex / group
                  style={url=$link.};
define n / 'Count';
define age / mean 'Average Age';
define height / mean 'Average Height';
run;

    

proc tabulate data=sashelp.class;
title 'with PROC TAB';
title2 'click on a link to go to URL';
class sex;
classlev sex /style={url=$link.};
var age height;
tables sex,
       n mean*(age height);
run;

   
ods html close;

View solution in original post

4 REPLIES 4
Reeza
Super User

I don't think it's available by default, but if you google drill down reports SAS you'll find multiple examples on how to build a custom report.

Cynthia_sas
SAS Super FREQ

Hi: Style={URL=} will not work with PROC FREQ, however. So the approach would be to generate the output with FREQ and/or TABULATE or REPORT (I'd probably pick PROC REPORT) and then do the drill downs using the CALL DEFINE in PROC REPORT. But you also have to make the "drill-down" files. For example in the above 2x2 table with a total in the column and a total at the end of the table, you have 9 cells that would each need to have a drill-down file, so there are a few different ways to plan for the names of the drill-down files (assuming they will be static):

               CAT 1     CAT 2           TOTAL

QUEUE A     qA_c1.html   qA_c2.html      qA_tot.html

QUEUE B     qB_c1.html   qB_c2.html      qB_tot.html

  

TOTAL       c1_tot.html  c2_tot.html     cat_tot.html

OR, do you really need 9 separate drill down files? You could show 1 drill-down page with for each row

               CAT 1       CAT 2           TOTAL

QUEUE A     qA_tot.html   qA_tot.html      qA_tot.html

QUEUE B     qB_tot.html   qB_tot.html      qB_tot.html

  

TOTAL       cat_tot.html  cat_tot.html      cat_tot.html

OR you could show 1 drill down page with for each column:

               CAT 1       CAT 2           TOTAL

QUEUE A     c1_tot.html   c2_tot.html      cat_tot.html

QUEUE B     c1_tot.html   c2_tot.html      cat_tot.html

    

TOTAL       c1_tot.html   c2_tot.html      cat_tot.html

  

:So, there are examples of doing drill-down, but it will not be easy to do with PROC FREQ. You will have to look to another procedure, like PROC REPORT and you will have to make ALL the files -- the "main" file and then all the drill-down files. And then you have to use the style={url=} syntax to build the link. Here's a simpler example, where the link is on the ROW variable. And I made separate files for just the males and the females and then nothing for the Total. But it should give you the idea of how much more complicated it would be to attach a link to the number in the cell.

Cynthia


** 1) First make the drill-down files, so you know the names;
ods html path='c:\temp' (url=none)
             file='Females.html' style=sasweb;
proc print data=sashelp.class noobs n;
title 'Data on Women';
title2 link='file_with_url.html' 'Go Back To Summary';
footnote link='Males.html' 'Detail Report for Men';
where sex = 'F';
var name age height;
run;
ods html close;

   

ods html path='c:\temp' (url=none)
             file='Males.html' style=sasweb;
proc print data=sashelp.class noobs n;
title 'Data on Men';
title2 link='file_with_url.html' 'Go Back To Summary';
footnote link='Females.html' 'Detail Report for Women';
where sex = 'M';
var name age height;
run;
ods html close;

    

** 2) Create a user-defined format with the names of the files;
proc format;
  value $link 'M' = 'Males.html'
              'F' = 'Females.html';
run;
ods listing close;


** 3) Now use the format in a URL= style override.;
ods html path='c:\temp' (url=none)
             file='file_with_url.html'
             style=sasweb;

 
proc report data=sashelp.class nowd;
title 'with PROC REPORT';
title2 'click on a link to see the detail data';
footnote ;
column sex n age height;
define sex / group
                  style={url=$link.};
define n / 'Count';
define age / mean 'Average Age';
define height / mean 'Average Height';
run;

    

proc tabulate data=sashelp.class;
title 'with PROC TAB';
title2 'click on a link to go to URL';
class sex;
classlev sex /style={url=$link.};
var age height;
tables sex,
       n mean*(age height);
run;

   
ods html close;

cxkev
Fluorite | Level 6

Thank you all for your help

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
  • 4 replies
  • 1181 views
  • 6 likes
  • 4 in conversation