turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Proc Report Drill Down

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-31-2012 03:21 AM

Hi

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ArpitSharma

10-31-2012 01:31 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ArpitSharma

10-31-2012 10:48 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

11-03-2012 11:01 PM

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;

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ArpitSharma

11-04-2012 01:01 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ArpitSharma

11-04-2012 09:27 PM

Sorry. I am not familiar with stored processes .

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

11-05-2012 12:39 AM

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;**

** end;**

** end;**

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