topic Re: Proc Report Drill Down in SAS Procedures

Proc Report Drill Down

ArpitSharma — Wed, 31 Oct 2012 07:21:12 GMT

I have the following dataset:

data test;

set sashelp.cars;

where

make in ("Acura","Audi") and Origin in("Asia","Europe") and Type in ("Sedan","Sports") ;

run;

This will have 23 observations in the dataset names "Test".

Now I write the following Code:

proc report data = test;

columns make origin type (n);

define make / group;

define type / group;

define origin / group;

run;

Thus you will see something like the following:

Make Origin Type n

Acura Asia Sedan 5

Sports 1

Audi Europe Sedan 13

Sports 4

I have computed my variable named "N".

My variable N has been highlighted in blue color with an underline.

And by providing it html links I have connected this stored process to another stored process.

I have passed parameters such that:

1. When I click on 5== I print the 5 records which has Make=Acura, Origin=Asia and Type=Sedan.

2. When I click on 1== I print the 1 record which has Make=Acura, Origin=Asia and Type=Sports.

My problem is:

When I click on 13:

I still get 5 records with Make=Acura, Origin=Asia and Type=Sedan.

When I click on 4:

I still get 1 record with Make=Acura, Origin=Asia and Type=Sports.

For hyperlink on 5&1 it does take parameters from the same observations but why does it not take parameters for the second group?

I hope I have explained the problem clearly. In case I haven't please let me know and I will try to provide it in more details.

Please give me reference to any paper which I should refer, to resolve my issue.

I am sure its doable but just not clear as to how.

PS:

I have not included the call define statement in the above code just to avoid extra code.

I have used the following paper and created drill down.

http://support.sas.com/resources/papers/proceedings11/050-2011.pdf

Thanks in advance for going through my problem.

Re: Proc Report Drill Down

ArtC — Wed, 31 Oct 2012 17:31:16 GMT

Your compute block was not included in your post so we cannot actually see the code. My initial guess is that there is a logic issue with the assignment of the path information (URL). More information can be found here:

http://www.wuss.org/proceedings09/09WUSSProceedings/papers/how/HOW-Carpenter.pdf

Re: Proc Report Drill Down

Ksharp — Thu, 01 Nov 2012 02:48:33 GMT

I coded it before for someone. Check it out:

https://communities.sas.com/message/126284#126284

Ksharp

Re: Proc Report Drill Down

ArpitSharma — Sun, 04 Nov 2012 03:01:42 GMT

Hi Sorry for not getting back early..

I have been trying to understand how proc report drill down works for which I have created two stored processes.

Name of Stored Process 1: Cars_1

Name of Stored Process 2: Cars_2

Here are the revised code.

PROBLEM 1:

When I drill down on Variable "N" it still gives me output for "SET1"

I want output of ('SET1', 'SET2', 'SET3').

PROBLEM 2:

When I drill down on any row other than the first row in the log I get missing values for

LEVEL1 and LEVEL2.

For the grouped variables does the value of that cell in the column go missing?

Please help me with the code and point out where I need to make changes.

I agree I have done some hard coding.

You can simply copy paste the code.

(Yes you will have to change the server address and path where you save the code.)

CODE Stored Process 1: Cars_1:

/************************************/

options missing='0';

data one;

input level1 :$2. level2 :$2. level3 :$2. across1 :$8. ;

datalines;

A1 B1 C1 SET1

A1 B1 C2 SET1

A1 B1 C3 SET1

A1 B2 C1 SET1

A1 B2 C2 SET1

A1 B2 C3 SET1

A1 B3 C1 SET1

A1 B3 C2 SET1

A1 B3 C3 SET1

A1 B1 C1 SET2

A1 B1 C2 SET2

A1 B1 C3 SET2

A1 B2 C1 SET2

A1 B2 C2 SET2

A1 B2 C3 SET2

A1 B3 C1 SET2

A1 B3 C2 SET2

A1 B3 C3 SET2

A1 B1 C1 SET3

A1 B1 C2 SET3

A1 B1 C3 SET3

A1 B2 C1 SET3

A1 B2 C2 SET3

A1 B2 C3 SET3

A1 B3 C1 SET3

A1 B3 C2 SET3

A1 B3 C3 SET3

A2 B1 C1 SET1

A2 B1 C2 SET1

A2 B1 C3 SET1

A2 B2 C1 SET1

A2 B2 C2 SET1

A2 B2 C3 SET1

A2 B3 C1 SET1

A2 B3 C2 SET1

A2 B3 C3 SET1

A2 B1 C1 SET2

A2 B1 C2 SET2

A2 B1 C3 SET2

A2 B2 C1 SET2

A2 B2 C2 SET2

A2 B2 C3 SET2

A2 B3 C1 SET2

A2 B3 C2 SET2

A2 B3 C3 SET2

A2 B1 C1 SET3

A2 B1 C2 SET3

A2 B1 C3 SET3

A2 B2 C1 SET3

A2 B2 C2 SET3

A2 B2 C3 SET3

A2 B3 C1 SET3

A2 B3 C2 SET3

A2 B3 C3 SET3

;

run;

%let max_col=6;

%let as4=SET1;

%let as5=SET2;

%let as6=SET3;

%let as7=SET1SET2SET3;

OPTIONS SYMBOLGEN mprint ;

%macro report;

proc report data=one SPANROWS;

columns level1 level2 level3 across1 (n);

define level1 / group;

define level2 / group;

define level3 / group;

define across1 / across ;

compute across1 ;

%do i=4 %to %eval(&max_col);

urlstring =

'http://MY SERVER ADDRESS/do?&_program=

/BI Content/MY STORED PROCESS PATH/

/CARS_2&test='||

trim(strip(level1||'|'||level2||'|'||level3||'|'||"&&as&i"));

call define ("_c%eval(&i)_", 'URL', urlstring);

%end;

endcomp ;

%PUT &TEST.;

compute N ;

%do i=7 %to 7;

/* %eval(&max_col);*/

urlstring =

'http://MY SERVER ADDRESS/do?&_program=

/BI Content/MY STORED PROCESS PATH/

/CARS_2&test='||

trim(strip(level1||'|'||level2||'|'||level3||'|'||"&&as&i"

/* "&&as&i"*/

));

call define ("_c%eval(&i)_", 'URL', urlstring);

%end;

endcomp ;

%PUT &TEST.;

%mend;

%report;

run;

/************************************/

CODE Stored Process 2: Cars_2:

/************************************/

/*%let test=A1|B1|C1|SET1SET2SET3;*/

/*%let test=A1|B1|C1|SET1;*/

%put &test.;

DATA ONE ;

MACRO = "&TEST." ;

RUN;

data _null_;

SET ONE ;

T_LEVEL1 = scan(macro,1,'|');

T_LEVEL2 = scan(macro,2,'|');

T_LEVEL3 = scan(macro,3,'|');

T_SET=SCAN(MACRO,4,'|');

call symput("LEVEL1",trim(T_LEVEL1));

call symput("LEVEL2",trim(T_LEVEL2));

call symput("LEVEL3",trim(T_LEVEL3));

call symput("SET",trim(t_SET));

run;

%PUT &SET.;

%MACRO START;

%LET ABCD=%SYSFUNC(LENGTH(&SET.));

%IF &ABCD.>4 %THEN %DO;

data _null_;

%let one=%substr(&set.,1,4);

%let two=%substr(&set.,5,4);

%let three=%substr(&set.,9,4);

run;

%let set=("&one.","&two.","&three.");

%put &set.;

%end;

%ELSE %DO;

%LET SET=("&SET.");

%END;

%mend;

%start;

%PUT &LEVEL1.;

%PUT &LEVEL2.;

%PUT &LEVEL3.;

%put &SET.;

data one;

input level1 :$2. level2 :$2. level3 :$2. across1 :$8. ;

datalines;

A1 B1 C1 SET1

A1 B1 C2 SET1

A1 B1 C3 SET1

A1 B2 C1 SET1

A1 B2 C2 SET1

A1 B2 C3 SET1

A1 B3 C1 SET1

A1 B3 C2 SET1

A1 B3 C3 SET1

A1 B1 C1 SET2

A1 B1 C2 SET2

A1 B1 C3 SET2

A1 B2 C1 SET2

A1 B2 C2 SET2

A1 B2 C3 SET2

A1 B3 C1 SET2

A1 B3 C2 SET2

A1 B3 C3 SET2

A1 B1 C1 SET3

A1 B1 C2 SET3

A1 B1 C3 SET3

A1 B2 C1 SET3

A1 B2 C2 SET3

A1 B2 C3 SET3

A1 B3 C1 SET3

A1 B3 C2 SET3

A1 B3 C3 SET3

A2 B1 C1 SET1

A2 B1 C2 SET1

A2 B1 C3 SET1

A2 B2 C1 SET1

A2 B2 C2 SET1

A2 B2 C3 SET1

A2 B3 C1 SET1

A2 B3 C2 SET1

A2 B3 C3 SET1

A2 B1 C1 SET2

A2 B1 C2 SET2

A2 B1 C3 SET2

A2 B2 C1 SET2

A2 B2 C2 SET2

A2 B2 C3 SET2

A2 B3 C1 SET2

A2 B3 C2 SET2

A2 B3 C3 SET2

A2 B1 C1 SET3

A2 B1 C2 SET3

A2 B1 C3 SET3

A2 B2 C1 SET3

A2 B2 C2 SET3

A2 B2 C3 SET3

A2 B3 C1 SET3

A2 B3 C2 SET3

A2 B3 C3 SET3

;

run;

PROC PRINT DATA=ONE;

WHERE

LEVEL1="&LEVEL1."

AND

LEVEL2="&LEVEL2."

AND

LEVEL3="&LEVEL3."

AND

ACROSS1 in &SET.

;

RUN;

/************************************/

Re: Proc Report Drill Down

shivas — Sun, 04 Nov 2012 18:01:42 GMT

Hi,

No SAS system to check but you can try this...Hope it helps...

Your compute block should be some thing like this..

compute across1 ;

length urlstring $500;

urlstring =

"http://nzapwa14.nndc.kp.org:7070/SASStoredProcess/do?&_program=

/BI Content/National/NAMSA/MSA_TEST/StoredProcesses

/CARS_2"||'&across1=' || urlencode(trim(left(level1||'|'||level2||'|'||level3||'|')));

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

endcomp ;

Thanks,

Shiva

Re: Proc Report Drill Down

Ksharp — Mon, 05 Nov 2012 02:27:40 GMT

Sorry. I am not familiar with stored processes .

Maybe you should post it at another forum about stored processes .

Re: Proc Report Drill Down

Cynthia_sas — Mon, 05 Nov 2012 05:39:43 GMT

Hi:

But the issue the OP is having is irrelevant to whether a stored process is involved or not. The fact is that when SAS and PROC REPORT make a report from GROUP items, the first row of the group contains a value and the second and subsequent rows of the group have blanks on the rows for that column.

You can prove this to yourself by running a program similar to the one below. Note that I removed the call to the stored process server with the URLSTRING variable. There's no point in calling a stored process until the "CALC" items correctly build the string you want to build.

So, if you look at the "CALC" items in the attached screen shot, produced with the attached program, you will see that the LEVEL1 (and LEVEL2) value is only available on the first row of the group.), is to "grab" the group items by testing for the "before" break point and then "grabbing" and holding the value in a temporary report variable. PROC REPORT does NOT have a Program Data Vector like the DATA step. So, just because you have a LEVEL1 and LEVEL2 value on every row in the data, does not mean that the value is available to PROC REPORT as it builds each report row. A group item in a PROC REPORT table has blanks for rows where the group items would be a duplicate value -- this is considered presentation quality -- to suppress the repetitious display of duplicate values. So showing a PROC PRINT of the data doesn't mean anything because PROC REPORT builds every report row a certain way and part of that method is to suppress the repetitious display of duplicate values for GROUP and ORDER items.

I used 3 separate compute blocks instead of a more complex single compute block to show the behavior that the OP observed. Until the OP has the compute blocks working correctly, there is no point in working on URLSTRING and/or building a URL to call a stored process. This is all PROC REPORT "internals" and basic PROC REPORT processing.

ACROSS variables does complicate things a bit, but there's a way around that -- the key to understanding is to get around the blank values for GROUP items whe there's more than 1 value in the report rows.

cynthia

data test;

length level1 level2 level3 $2 across1 $4;

do level1 = 'A1', 'A2';

do level2 = 'B1', 'B2';

do level3 = 'C1', 'C2', 'C3';

do across1 = 'SET1', 'SET2', 'SET3';

** output multiple obs based on RANUNI value;

if ranuni(0) gt .4 then output;

if ranuni(0) le .333 then output;

if ranuni(0) gt .7235 then output;

if ranuni(0) le .2111 then output;

output; output;

end;

run;

ods listing close;

ods html file='c:\temp\use_grp_val.html' style=sasweb;

proc report data = test nowd spanrows;

title '1) Show compute without "grabbing" values';

columns level1 level2 level3 across1 (n) calc1 calc2 calc3;

define level1 / group;

define level2 / group;

define level3 / group;

define across1 / across;

define calc1 / computed f=$50.;

define calc2 / computed f=$50.;

define calc3 / computed f=$50.;

compute calc1 / character length=50;

calc1 = level1||level2||level3||"SET1";

endcomp;

compute calc2 / character length=50;

calc2 = level1||level2||level3||"SET2";

endcomp;

compute calc3 / character length=50;

calc3 = level1||level2||level3||"SET3";

endcomp;

run;

ods html close;

cynthia