The SAS Output Delivery System and reporting techniques

Drill down summary report to Detailed report.

Reply
Frequent Contributor
Posts: 78

Drill down summary report to Detailed report.

I have been trying to create it since hours so thought to put here to get some advices as I have got previously..However to start with like to let you know that the code would be running on zOS Operating system(Mainframe) and the HTML output is going to the PDSE dataset...


After processing some raw data an intermittent SAS dataset is created which hold output like below which I was displaying on the ODS HTML output using the PROC REPORT ...Now a requirement is changed and it's required that instead of showing output like below it should be summarized by Max value of CPU_AVG from the below produced output so say on 1DEC14 UKDUK02 has 6 observation with highest value as "92" that should be what to be on Main HTML output ...So I feeded a PROC SUMMARY and had MAX calculated ....Since it's summary now I need to have a drill down to other proc report output which has all those 6 observations ....


So basically requirement is Summary which has a UKDUK02 with just one observation of 92 shown however that should have click link to detail output with all 6 observation shown...


Date Time Grid   Cluster Dlib            Mach  cpu_avg

01DEC14 6:00 00001 04 UKDUKC02 H1234 96    

6:00 00008 05  UKDUKC03 H6666 85   

6:15 00008 04  UKDUKC02 H1234 88   

20:15 00008 05 UKDUKC03 H6666 90   

20:30 00008 04 UKDUKC02 H1234 83  

20:30 00008 05 UKDUKC03 H6666 86  

21:15 00008 04 UKDUKC02 H1234 82  

21:15 00008 05 UKDUKC03 H6666 81   

21:30 00008 04 UKDUKC02 H1234 92   

21:45 00008 04 UKDUKC02 H1234 85    

22:00 00008 04 UKDUKC02 H1234 88

22:00 00008 02 UKDUKC01 H5678 88

23:00 00008 02 UKDUKC01 H5678 90

Now I believe it's possible using the DRILL DOwn way I like to have it via Macro as above output values are just sample it change drastically...


Any help ??? thanks

Frequent Contributor
Posts: 78

Re: Drill down summary report to Detailed report.

This is what I believe is something to work to achieve above...(kind of example/test run with some other data)

FILENAME H_OUT 'NBKEODV.OUTPUT1.HTML';                              

%LET RPT = C ;                                                      

DATA CARSALES_DATA ;                                                

  INPUT STATE $1-13 MANUFACTURER $15-24 SALES 25-32 ;               

  DATALINES;                                                        

CAL           BMW       3340457                                     

CAL           GMC       5411869                                     

CAL           FORD      9742527                                     

CAL           KIA       1249542                                     

CAL           AUDI      1506660                                     

CAL           HYUNDAI   9917911                                     

CAL           NISSAN    5439759                                     

CAL           CHEVROLET 1302291                                     

CAL           TOYOTA    1597252                                     

FLO           GMC       7474090                                     

FLO           FORD      4354102                                     

FLO           KIA       5113239                                    

FLO           TOYOTA    7803512                             

FLO           HYUNDAI   4281749                             

FLO           HONDA     1942236                             

NEW           GMC       5073937                             

NEW           FORD      9468916                             

NEW           KIA       7431957                             

NEW           TOYOTA    1048175                             

OHI           BMW       6084361                             

OHI           GMC       4589571                             

OHI           FORD      7745341                             

MIC           BMW       6883939                             

MIC           GMC       4067915                             

MIC           FORD      1058419                             

MAS           GMC       9663352                             

MAS           FORD      2096304                             

MAS           KIA       3420529                             

MAS           TOYOTA    9455168                             

;                                                           

DATA HTML_CARSALES_DATA ;                                   

SET CARSALES_DATA ;                                         

LENGTH HLINK $256. ;                                       

HLINK='<A '||COMPRESS('HREF="NBKEODV.OUTPUT1.HTML('||STATE||')         

>'||TRIM(STATE)||'</A>');                                              

                                                                       

PROC PRINT DATA=HTML_CARSALES_DATA;RUN;                                

                                                                       

ODS LISTING CLOSE ;                                                    

ODS HTML BODY = "&RPT" PATH="NBKEODV.OUTPUT1.HTML" (URL=NONE);         

PROC REPORT DATA = HTML_CARSALES_DATA NOWD;                            

  COLUMN HLINK                                                         

         SALES ;                                                       

  DEFINE HLINK / GROUP "STATE" CENTER ;                                

  DEFINE SALES / ANALYSIS SUM "SALES" FORMAT=DOLLAR15.2 ;              

  RBREAK AFTER / SUMMARIZE ;                                           

RUN ;                                                                  

TITLE ;                                                                

ODS HTML CLOSE ;                                                       

ODS LISTING ;                                                          

PROC FREQ DATA = HTML_CARSALES_DATA NOPRINT ;                          

  TABLE STATE / OUT=F_STATE LIST ;                                     

RUN ;                                                                  

DATA _NULL_ ;                                                         

  SET F_STATE END=EOF;                                                 

  CALL SYMPUT(CATS('STURL',_N_),COMPRESS(STATE,,'KA'));                

  CALL SYMPUT(CATS('STDIS',_N_),STATE);                                

  IF EOF THEN CALL SYMPUT('STNUM',_N_);                                

RUN ;                                                                 

%MACRO BUILD_DYNAMIC_REPORT;                                          

   ODS LISTING CLOSE ;                                                 

   %DO N=1 %TO &STNUM ;                                                

ODS HTML BODY = "&RPT&&STURL&N" PATH="NBKEODV.OUTPUT1.HTML" (URL=NONE)

              STYLE=STYLES.SASWEB                                      

              ;                                                        

     TITLE "DRILL DOWN REPORTING";                                     

     TITLE2 "&&STDIS&N - SALES BY MANUFACTURER";                       

PROC REPORT DATA = HTML_CARSALES_DATA (WHERE=(STATE="&&STDIS&N")) NOWD;

       COLUMN MANUFACTURER                                             

              SALES ;                                                  

       DEFINE MANUFACTURER / DISPLAY "MANUFACTURER" CENTER ;           

       DEFINE SALES / ANALYSIS SUM "SALES" FORMAT=DOLLAR15.2 ;         

     RBREAK AFTER / SUMMARIZE ;                                        

RUN ;                                                                 

     TITLE ;                                                          

    ODS HTML CLOSE ;                                           

  %END ; /*%DO N=1 %TO &STNUM ;*/                              

  ODS LISTING ;                                                

%MEND;                                                         

%BUILD_DYNAMIC_REPORT;                                        

However HREF doesn't seems to be getting correctly created as it's not pointing to the PDS members ...any idea ??

SAS Super FREQ
Posts: 8,864

Re: Drill down summary report to Detailed report.

Hi: The Anchor tag needs to be syntactically correct if you are building it yourself. But generally you also need to turn off the "regular" protection of the < and > symbols by using the Protectspecialchars=off style attribute.

A question for Tech Support would be whether you can actually point to a PDSE for drill down. Generally URLs point to a valid WEB SERVER address, not a physical file like a PDSE.

cynthia.

Frequent Contributor
Posts: 78

Re: Drill down summary report to Detailed report.

Posted in reply to Cynthia_sas

I believe with the MVSDS services it's possible and I see members are being created but not syntactically correct Anchor Tag....Do you think i need to contact Tech support on it?

SAS Super FREQ
Posts: 8,864

Re: Drill down summary report to Detailed report.

Yes, I think that Tech Support is your best resource for a question like this.

cynthia

Ask a Question
Discussion stats
  • 4 replies
  • 596 views
  • 0 likes
  • 2 in conversation