topic Re: How to do a Drill Down in SAS Procedures

How to do a Drill Down

BETO — Thu, 03 May 2012 18:41:02 GMT

Hi I need assistance in creating a drill down report.

I have a table Dep it has 3 unique values Test1, test2, test3. Each value has a col of true an false . How can I get to

test1 5 True 10 total 50%

test2 4 True 10 total 40%

test3 8 True 10 total 80%

And be able to click on the test1 to see the 10 entries clcik again an group it back together?

I would like to export it if possbile to a Excel format if not possble than a HTML.

Thanks again for you assitance

Re: How to do a Drill Down

Cynthia_sas — Thu, 03 May 2012 21:17:14 GMT

Hi:

It almost sounds to me like you want an Excel pivot table when you say "be able to click on the test1 to see the 10 entries click again and group it back together".

When you use SAS, you can simulate drill down from one report (such as a summary report) to another report (such as a detail report) using ODS HTML. Also with ODS, in a separate method, you can use ODS MSOFFICE2K_X to create an HTML file that will open into a pivot table in Excel because of additional JavaScript that's added to the HTML file.

So, the code below illustrates the first kind of drill-down example, where you have separate files and you drill-down from the summary report (the REPORT or TABULATE) to a detail report.

For more information about the second method, using ODS to make HTML files that can open into pivot tables, refer to this page on using ODS TAGSETS.MSOFFICE2K_X:

http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html

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;
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;
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;
proc format;
value $link 'M' = 'Males.html'
'F' = 'Females.html';
value agefmt 13.222222-13.22223 = 'http://www.sas.com'
other = 'http://www.setgame.com';
run;
ods listing close;

** 3) Now use the format in a URL= style override.;
**    Note how the age format goes to a web site instead of;
**    a "drill-down" file. This is another use for URL style attribute.;
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'
             style={url=agefmt.};
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,
mean*(age*{style={url=agefmt.}} height);
run;
ods html close;

Re: How to do a Drill Down

BETO — Fri, 04 May 2012 04:23:56 GMT

Hi Cynthia,

Enclose is my proc tab how can I apply your examples to the script? "Compliance is text (True/False)... Thanks again

proc format;

picture tabpct (round)

low-high='009.00%';

run;

ODS HTML3 file='H:\SCR.xls' style=minimal;

options missing=' ';

proc tabulate data=SCR1 f=10.2 S=[backGROUND=light yellow JUST=C] noseps;

where Vendor='TEST1;

class DateRange / order =data;

class VSB Compliance;

classlev Compliance /S=[BACKGROUND=light grey];

keylabel all='Total';

table VSB*(Compliance all), daterange*(n='N'*f=8.0 pctn<Compliance all>='Percent'*f=tabpct.)All pctn<Compliance all>='Overall'*f=tabpct.

/RTS=50 MISSTEXT ='0' box='Unscheduled'

;

run;

ODS HTML3 close;

Re: How to do a Drill Down

Cynthia_sas — Fri, 04 May 2012 13:04:29 GMT

Hi:

I'm sorry. I don't quite get it.

At first, you said you wanted to "drill-down" -- so my example shows how to build 2 separate files (one for females and one for males). Then I have a PROC REPORT summary report (which allows drill-down to the 2 files) and I also have a PROC TABULATE summary report (which also allows drill-down to the 2 files). In my PROC TABULATE, I associate the CLASSLEV for the SEX variable with a user-defined format:

classlev sex /style={url=$link.};

and the URL= style attribute causes a hyperlink to be built in the summary file. Then, when the user clicks on the hyperlink in the summary file, the hyperlink causes either females.html or males.html to load into the browser. Did you run my code?

I don't understand what you mean when you say 'how can I apply your examples to the script? "Compliance is text (True/False)" ' Do you want the string "Compliance is text (True/False)" to be a title? A footnote? A row or column label? A label in the box area at the intersection of row and column headers? Perhaps you want that string as a "flyover" or popup window?

It is one text string, so I can't imagine that you want the user to drill-down to a separate file that contains just that text string? If you want that string to be a label for the COMPLIANCE variable, then you would assign it either in a LABEL statement inside the TABULATE step or you would assign it in the TABLE statement. If you want that text string as a label in the BOX area, then you would assign the label in the BOX= option on the TABLE statement. If you want that text string as a title, then you would use it in a TITLE statement. If you want that single text string as a "drill-down", then you would need to make a file which contained the text string and then follow the general steps that I've shown in order to set a URL for that file. If you want that text string as a popup box or "flyover" window, then you would use the FLYOVER style attribute.

However, I am not sure that some of these techniques (such as URL= or FLYOVER=) will continue to work when you open the HTML file with Excel. I know that URL= and FLYOVER= work in a browser, but Excel is not a browser -- and just because you name your file with .XLS as the file extension, that doesn't mean you are creating a true, binary Excel file. You are merely creating a file that Excel knows how to open. Whether or not Excel respects all the HTML features you use (such as hyperlinks or flyover boxes) is something you have to test.

cynthia

Re: How to do a Drill Down

BETO — Mon, 07 May 2012 06:21:14 GMT

Hi Cynthia,

Thank you for your response... I followed your code that creates A PROC REPORTwhich after tweaking to my data it work. What I needed your help on is how to get a % included into the Proc Report for example using your script height and Age if those varabiles were just numbers I want to get Age divided into height to get me the % in the 1st level not when you drill down... What I'm doing is getting total # of opportunitiies divide by # of comliance... Thanks again Cynthia for the help...

Re: How to do a Drill Down

ramak — Tue, 14 Jun 2016 18:10:53 GMT

Dear Madam,

how to prepare male html and female html and how to give path.

Re: How to do a Drill Down

Cynthia_sas — Tue, 14 Jun 2016 18:18:40 GMT

Please look at the code marked "1)" in the code that I posted in 2012. The male.html and the female.html files are created as the first step of the entire program. In my code I specify the path location on my personal computer's C: drive as c:\temp in the PATH= option. I don't know where you are saving the HTML files, but you should write the files to a physical folder location where you have write access.

cynthia