topic Drill down summary report to Detailed report. in ODS and Base Reporting

Drill down summary report to Detailed report.

rkumar23 — Wed, 03 Dec 2014 07:26:21 GMT

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

Re: Drill down summary report to Detailed report.

rkumar23 — Wed, 03 Dec 2014 13:17:21 GMT

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 ??

Re: Drill down summary report to Detailed report.

Cynthia_sas — Wed, 03 Dec 2014 14:21:14 GMT

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.

Re: Drill down summary report to Detailed report.

rkumar23 — Thu, 04 Dec 2014 03:28:29 GMT

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?

Re: Drill down summary report to Detailed report.

Cynthia_sas — Thu, 04 Dec 2014 15:44:21 GMT

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

cynthia