topic Re: how to create link or drill down by proc report in a dataset in SAS Programming

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

Ashwini — Wed, 09 May 2012 10:17:50 GMT

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

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

Ashwini — Thu, 10 May 2012 01:34:58 GMT

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

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

Ksharp — Thu, 10 May 2012 09:29:09 GMT

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

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

Ashwini — Tue, 15 May 2012 02:14:21 GMT

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

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

Ksharp — Tue, 15 May 2012 02:29:33 GMT

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

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

shivas — Tue, 15 May 2012 06:04:11 GMT

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