how to create link or drill down by proc report in a dataset

[Ashwini]

I have a dataset student detail like

Strolno stname class math science English history geography division
1       A      5th   77   70     80     90      70         1st 
2       B      6th   55   30     80     90      40         2ND
3       C      5th   40   70     30     30      30         3RD
4       D      7th   60   30     80     90      70         2ND
5       E      5th   77   70     80     90      70         1st
6       F      6th   66   90     80     90      90         1st
7       G      6th   81   70     80     90      70         1st
8       H      5th   40   70     80     90      70         2ND
9       I      7th   98   70     80     90      70         1st
10      J      7th   30   30     30     30      30         3RD
11      K      6th   86   70     80     90      70         1st
12      L      5th   77   70     80     90      70         1st
13      M      7th   87   70     80     90      70         1st
14      N      6th   30   60     30     90      30         3RD
15      O      5th   88   70     80     90      70         1st
16      P      6th   55   70     80     90      70         1st
17      Q      7th   50   60     40     90      70         1st
18      R      5th   80   70     80     90      70         1st

1.we have to create a dataset student summary by proc report which contain only below colom

strolno stname class total division

2.when we click on the strolno 1 ofdataset student summary

it shows below detail

strol stname science English history geography total division

2.if we click on 1st in division dataset student summary
it show all the student detail who got 1st division like

stno stname scince enlish history geography total division

Kindly help to write this program

Thanks,
Regards,
Ashwini

[Ashwini]

We have to create a  student summery dataset which contain below columns

Strolno stname class division

1              A      5th        1st 

2               B      6th     2ND

3                C      5th    3RD

4                 D      7th    2ND

5                 E      5th    1st

6                F      6th    1st

7                G      6th    1st

8                 H      5th    2ND

9                  I      7th      1st

10                J      7th    3RD

11                K      6th   1st

12                L      5th   1st

13                M      7th   1st

14                 N      6th    3RD

15                 O      5th    1st

16      P      6th     1st

17      Q      7th     1st

18      R      5th     1st

I want to create hyper link or drill down in a table in sas as above mention student summery  dataset for example

1.if I click on the strolno 1 it shows below dataset like

Strolno stname class math science English history geography division

1       A      5th   77   70     80     90      70         1st 

2.if I click on stname that is A it shows detali data of A like

Strolno stname class math science English history geography division

1                   A      5th   77                  70     80     90      70         1st 

3.if I click on class 6th then it shows below dataset

Strolno stname class math science English history geography division

        2       B      6th        55          30     80     90        40               2ND

       6       F       6th        66          90     80     90         90             1st

       7       G      6th         81         70     80     90         70            1st

       11      K      6th         86         70     80     90          70           1st

      14      N      6th         30         60     30     90          30         3RD

      16      P      6th          55        70     80     90          70         1st

Kindly help me to write the code above query.

Please reply me ASAP .

Thanks and regards ,

Ashwini

[Ksharp]

OK. How about:

Please Open main.html under c:\ by using IE firstly .

data have;
input Strolno stname $ class $ math science English history geography division $;
cards;
1       A      5th   77   70     80     90      70         1st 
2       B      6th   55   30     80     90      40         2ND
3       C      5th   40   70     30     30      30         3RD
4       D      7th   60   30     80     90      70         2ND
5       E      5th   77   70     80     90      70         1st
6       F      6th   66   90     80     90      90         1st
7       G      6th   81   70     80     90      70         1st
8       H      5th   40   70     80     90      70         2ND
9       I      7th   98   70     80     90      70         1st
10      J      7th   30   30     30     30      30         3RD
11      K      6th   86   70     80     90      70         1st
12      L      5th   77   70     80     90      70         1st
13      M      7th   87   70     80     90      70         1st
14      N      6th   30   60     30     90      30         3RD
15      O      5th   88   70     80     90      70         1st
16      P      6th   55   70     80     90      70         1st
17      Q      7th   50   60     40     90      70         1st
18      R      5th   80   70     80     90      70         1st
;
run;

%macro Strolno(value=);
ods listing close;
ods html path='c:\' body="&value..html" style=sasweb;
title;
proc report data=have(where=(Strolno=&value)) nowd;
run;
ods listing;
%mend;


%macro stname(value=);
ods listing close;
ods html path='c:\' body="&value..html" style=sasweb;
title;
proc report data=have(where=(stname="&value")) nowd;
run;
ods listing;
%mend;


%macro class(value=);
ods listing close;
ods html path='c:\' body="&value..html" style=sasweb;
title;
proc report data=have(where=(class="&value")) nowd;
run;
ods listing;
%mend;

proc sql;
create table Strolno as select distinct Strolno from have;
create table stname as select distinct stname from have;
create table class as select distinct class from have;
quit;

data _null_;
 set Strolno;
 call execute(cats('%Strolno(value=',Strolno,')'));
run;

data _null_;
 set stname;
 call execute(cats('%stname(value=',stname,')'));
run;

data _null_;
 set class;
 call execute(cats('%class(value=',class,')'));
run;







ods listing close;
ods html path='c:\' body="main.html" style=sasweb;
title;
proc report data=have nowd ;
column Strolno stname class division ;
define  Strolno /display;
compute Strolno;
call define(_col_,'url',cats(Strolno,'.html')) ;
call define(_col_,'style','style={HREFTARGET="_blank"}');
endcomp;
compute stname;
call define(_col_,'url',cats(stname,'.html')) ;
call define(_col_,'style','style={HREFTARGET="_blank"}');
endcomp;
compute class;
call define(_col_,'url',cats(class,'.html')) ;
call define(_col_,'style','style={HREFTARGET="_blank"}');
endcomp;
run;
ods listing;

P.S. plz remember my name :  Xia Keshan   

Ksharp

[Ashwini]

Dear Ksharp,

I am working in sas egrc system .Here  only data are available in dataset which are in informatted value .So help me how to create the hyper link in sas GRC .

Thanks,

Regards,

Ashwini

[Ksharp]

Hi.

Sorry. I can't help you. I don't have  any experience of SAS GRC . Maybe you need to talk with SAS Technical Support .

Ksharp

[shivas]

Hi Aswini,

If you are using SAS EGRC system or oprisk monitor then you need to write a stored process.I have written for one(division) I hope you can write for the remaining.

First SP:

LIBNAME D_DashB BASE "F:\DATA_Dashboards\DATA";

ods html body=_webout;

proc report data=D_DashB.have nowd ;

column Strolno stname  class  math science English history geography division ;

   define strolno/'strolno' display;

define stname/'stname' display;

define class/'class' display;

define math/'math' display;

define science/'science' display;

define English/'English' display;

define history/'history' display;

define geography/'geography' display;

define division/'division' display;

compute division;

length nexturl $500;

  nexturl = "&_URL?_program=/Users/sasdemo/My Folder/division"

   || '&division='|| urlencode(trim(left(division))) ;

  call define(_col_,"URL",nexturl);

   endcomp;

run;

ods html close;

Second SP(Division);

LIBNAME D_DashB BASE "F:\DATA_Dashboards\DATA";

proc print data=D_DashB.have(where=(division="&division"));

run;

Thanks,

Shiva