Proc Report By-Grouping and Sorting

[Filipvdr]

Hello,

I want to do a proc report on a dataset which is sorted by date with by variable.
Also I have defined for 2 variables 'Group'.

But when I do this by variables, the date gets unsorted. The group works correctly.

So I find a solution, create a second date which I can sort on but don't print it out ( order order=internal noprint; )

But then the problem is that my grouping doesn't work correctly, because it performs grouping without doing the sort on the second date I think.


MY code:

proc sort data=output_data; by datim; run;

proc report data=output_data nowd style(report) = [rules=groups cellspacing=0pt cellpadding=2pt];
column ColorValueField Datim datim2 Operation Step PictureLink COL_INS_ID USER_ID FACILITY Route EVENT_TYPE HoldCode Annotation LotPriority T_Duration NrWafers SelToolList
OperParValue EVENT_ROW_ID PreferredTool MainStepService Standard ProcessEngineer BatchEngineer Program ;
define USER_ID / display noprint;
define datim2 / order order=internal noprint;
define FACILITY / display noprint ;
define route / display noprint ;
define Datim / display;
define Operation / group;
define Step / group;
define EVENT_TYPE / display ;
define HoldCode / display ;
define Annotation / display ;
define LotPriority / display 'Prio';
define T_Duration / display;
define NrWafers / display;
define SelToolList / display ;
define PreferredTool / display ;
define MainStepService / display ;
define Standard / display ;
define OperParValue / display noprint ;
define EVENT_ROW_ID / display noprint;
define PictureLink / display noprint;
define COL_INS_ID/ display noprint;
define ColorValueField / display noprint ;
define ProcessEngineer / display noprint ;
define BatchEngineer / display noprint ;
define Program / display noprint ;
by Facility Route notsorted;


any help??

[Cynthia_sas]

Hi;
Are you seeing ANY error messages in the log. When you say that "my grouping doesn't work correctly", it's not clear to me whether you are talking about Facility and Route or DATIM2 with Facility and Route. This is where an abbreviated example of the data would be useful...at least FACILITY, ROUTE, DATIM, DATIM2, and a few of the other variables like OPERATION and STEP-- just enough to see the unsorted order and for you to explain what order you want or explain how things are not working the way you expect. It would be useful to know why OPERATION and STEP are GROUP items and all the other items are either DISPLAY or ORDER items.

I notice that your PROC SORT is sorting by the DATIM variable, but your ORDER item in PROC REPORT is DATIM2 -- are you certain that DATIM and DATIM2 hold -exactly- the same value??? The other thing I'm curious about is the BY statement for
[pre]
by Facility Route notsorted;
[/pre]

How do you know that the DATIM sort is not messing up Facility and Route order somehow???

In earlier versions of SAS, it was recommended that you have all your grouping/ordering variables listed first in your COLUMN statement to make sure that the report rows were in the order you want. In SAS 9.2, it is possible to have DISPLAY items to the left of the ORDER item, but you MIGHT try putting DATIM2 first in the COLUMN statement...or change your PROC SORT to include FACILITY, ROUTE and DATIM2.

I would expect you to report that you are seeing a note like this:
[pre]
NOTE: Groups are not created because the usage of XXXXXX is DISPLAY. To avoid this note,
change all GROUP variables to ORDER variables.
[/pre]

cynthia

[Filipvdr]

Previous data sets:

[pre]

data all_append_and_edc_and_vars2;
set all_append_and_edc_and_vars;
datim2 = datim;
run;

proc sort data=all_append_and_edc_and_vars2; by route; run;

data routes(keep = route Program ProcessEngineer BatchEngineer);
set RF300L3.W_status_wiplot;
where Lot_ID = "&sel_lot";
run;

proc sort data=routes; by route; run;

data output_data;
merge all_append_and_edc_and_vars2 routes;
by route;
run;

[/pre]

This is the output (copied from HTML page)

Header:Lot: P110006 Route: SS_RECAP4 Facility: PLINE200
PE: SHAHAR BE: DUBOISB Program: PR MEMS-BASED PROJECTS Rowheader
Datim Operation Step EVENT_TYPE HoldCode Annotation Prio T_Duration NrWafers SelToolList PreferredTool MainStepService Standard
Data
04JAN11:10:18:07 7000-PRODUCT_INFORMATION 01-LotStart Create 4 0:00 23 NoTool-sProductInfo sProductInfo Std
04JAN11:10:18:11 7000-PRODUCT_INFORMATION 01-LotStart Hold Lot Fmhl Wating For Fabmanager to Relea... 4 141:32 23 NoTool-sProductInfo sProductInfo Std
10JAN11:07:50:32 7000-PRODUCT_INFORMATION 01-LotStart Release Lot -Fmhl 4 0:01 23 NoTool-sProductInfo sProductInfo Std
10JAN11:07:51:11 7001-WAFER_SELECT 03-ComposeLot Move Out 4 1:51 23 INNOLAS INNOLAS sComposeLot Std
10JAN11:09:41:32 7001-WAFER_SELECT 03-ComposeLot Object Annotation Dispatched for service: PLINE2... 4 0:00 . INNOLAS sComposeLot Std
10JAN11:09:41:54 7001-WAFER_SELECT 03-ComposeLot Move In 4 0:49 23 INNOLAS INNOLAS sComposeLot Std
10JAN11:09:41:57 7001-WAFER_SELECT 03-ComposeLot Event Annotation Entity: PLINE200.INNOLASMode :... 4 0:00 . INNOLAS sComposeLot Std
10JAN11:09:42:48 7001-WAFER_SELECT 03-ComposeLot VIEW EDC DATA 4 0:00 . INNOLAS sComposeLot Std
10JAN11:09:42:53 7001-WAFER_SELECT 03-ComposeLot Event Annotation Entity : PLINE200.INNOLASMode ... 4 0:00 . INNOLAS sComposeLot Std
10JAN11:09:43:25 7001-WAFER_SELECT 03-ComposeLot VIEW EDC DATA 4 0:00 . INNOLAS sComposeLot Std
10JAN11:09:43:29 7001-WAFER_SELECT 03-ComposeLot Event Annotation Entity : PLINE200.INNOLASMode ... 4 0:00 . INNOLAS sComposeLot Std
10JAN11:09:44:01 7001-WAFER_SELECT 03-ComposeLot VIEW EDC DATA 4 0:00 . INNOLAS sComposeLot Std
10JAN11:09:44:05 7001-WAFER_SELECT 03-ComposeLot Event Annotation Entity : PLINE200.INNOLASMode ... 4 0:00 . INNOLAS sComposeLot Std
10JAN11:10:30:22 7001-WAFER_SELECT 03-ComposeLot Object Annotation Dispatched for service: PLINE2... 4 0:00 . INNOLAS sComposeLot Std
10JAN11:10:30:31 8600-DRYOX_OXIDATION 02-Clean Move Out 4 0:50 23 F5_2;POSEIDON;SORT_OVENS P3_NITRIDE sDeposDryOx Std
10JAN11:11:20:36 8600-DRYOX_OXIDATION 02-Clean Hold Lot EqFail spc to do na shutdown 4 22:09 23 F5_2;POSEIDON;SORT_OVENS P3_NITRIDE sDeposDryOx Std
11JAN11:09:29:59 8600-DRYOX_OXIDATION 02-Clean Release Lot -EqFail Dryox is UP 4 4:02 23 F5_2;POSEIDON;SORT_OVENS P3_NITRIDE sDeposDryOx Std
11JAN11:13:30:49 8600-DRYOX_OXIDATION 02-Clean Object Annotation Dispatched for service: PLINE2... 4 0:00 . P3_NITRIDE sDeposDryOx Std
11JAN11:13:31:29 8600-DRYOX_OXIDATION 02-Clean Move In 4 1:02 23 F5_2;POSEIDON;SORT_OVENS P3_NITRIDE sDeposDryOx Std
11JAN11:13:39:11 8600-DRYOX_OXIDATION 02-Clean Event Annotation Entity : PLINE200.SORT_OVENSMo... 4 0:00 . P3_NITRIDE sDeposDryOx Std
11JAN11:13:44:44 8600-DRYOX_OXIDATION 02-Clean Object Annotation Dispatched for service: PLINE2... 4 0:00 . P3_NITRIDE sDeposDryOx Std


it continues for many many rows: result: HTML page looks very very crowded, so customer wants to remove all fields which are recurring ( grouping is needed ).

Also I have this object that changes from route and facility. If the object changes, i want a different table (with different header information). So I need byvariables facility and route.

But it is like a history of the object, so i want to sort it on date. But sorting on Datim doesn't work, so I added datim2. Now the sorting works, but the grouping doesn't.

It is a hard example, I know 🙂
All these outputs use the same input data set, sorted the same way

Output example without grouping:
http://img232.imageshack.us/i/sasforum.jpg/

Output example with grouping:
http://img190.imageshack.us/i/sasforum2.jpg/

here you can see that it doesn't work correctly, same values get repeated under each other for operation and step.

Output example with grouping but wrong sort:
/*define datim2 / order order=internal noprint;*/
http://img22.imageshack.us/i/sasforum3.jpg/
look at the datim fields...


So I hope it is clear enough now to give me some help 🙂

[Cynthia_sas]

Hi:
Both ORDER and GROUP should suppress the repetitious display of duplicate values. Your picture looks like a detail report to me and not a GROUPed report. You did not say whether you were seeing this note:
[pre]
NOTE: Groups are not created because the usage of XXXXXX is DISPLAY. To avoid this note,change all GROUP variables to ORDER variables.
[/pre]

IF you are seeing that note, then that is a clue that behind the scenes, your GROUP items are being changed to ORDER items and the NOTE is issued to warn you that you are using GROUP when you should be using ORDER.

Seeing the INPUT data -- not the OUTPUT data is the most useful. I still don't understand, what is wrong with the different reports. The print is very tiny and the zoom level does not go big enough to see the data. In the snippets of output that you have shown on IMAGESHACK, the order looks OK to me. DATIM2 is the first ORDERING variable and if I look at that column and ONLY that column -- you have very little repetition in the DATIM field, because your times are down to the second -- but that ordering seems to be OK.

In the pictures that you've posted, I can see that the order is DIFFERENT, and I can see that some repetitious values are not suppressed -- but I chalk that up to your COMBINATION of ordering variables not being unique or your SORT before PROC REPORT being wrong or the placement of the variables in the COLUMN statement to be wrong.

However, only you understand what you expect that you are not seeing. And, since the 3 images seem to be from different parts of the report, I don't know what I'm looking for. That's why seeing a snippet of INPUT data -- just 5 or 6 lines that illustrate the general INPUT data and then showing what's coming out of your code for just that 5 or 6 lines. And then saying what you WANT the output to be like -- that would be very useful. Otherwise, what I think is wrong may not be the same as what you think is wrong. Or, I'm seeing something and not interpreting it the same way that you are.

If you want someone to look at ALL of your DATA and ALL of your output, then your best bet for help is to open a track with Tech Support -- they can look at ALL of your code, ALL of your data and ALL of your output.

All I can tell you is that PROC REPORT allows the COLUMN statement to "rule" when it comes to ORDERing items. Using ORDER=INTERNAL on DATIM2 is only going to work for you if you can GUARANTEE that the data is ALREADY in the order that you want by the time PROC REPORT gets the data. So you have to consider that the FIRST ORDERed item on your COLUMN statement is DATIM2, followed by OPERATION and then STEP -- so that means STEP will be ordered within OPERATION which will be ORDERED within DATIM2. If you want DATIM2 to be ordered within OPERATION, then you would need to list OPERATION first in the COLUMN statement before DATIM2.

Consider the little bit of data (15 lines below) it is fake data -- and not very much fake data only sort of based on what I could read in your screen shot and your HTML snippet. I can ENORMOUSLY impact whether duplicate values are suppressed or not by just changing the order of variables in the COLUMN staement. And this is without using BY group processing or sorting into the MIX -- just using the COLUMN statement behavior -- so in my opinion, you need to really understand how the order of variables on the COLUMN statement impacts the suppression of repetitious values on the final report.

Perhaps this example will get you pointed in the right direction as far as how to fix your issue.

cynthia
[pre]
data stuff;
length operation $20 step $15 colorvaluefield $25 picturelink $50;
infile datalines dlm='~';
input User_ID operation $ step $ n1 n2 n3 datim : anydtdtm16.;
datim2 = datim;
if n1 lt 107 then colorvaluefield='cxdddddd';
else colorvaluefield='cyan';
picturelink = catx('^',user_id,operation,step);
format datim datim2 datetime24.;
return;
datalines;
1 ~7001-WAFER_SELECT~ 01-LotStart~ 101~ 69.0123~ 112.5 ~10JAN11 07:51:11~
2 ~7001-WAFER_SELECT~ 01-LotStart~ 102~ 56.5123~ 184.0 ~10JAN11 07:51:12~
3 ~7001-WAFER_SELECT~ 01-LotStart~ 103~ 65.3234~ 298.0 ~10JAN11 09:42:54~
4 ~7001-WAFER_SELECT~ 01-LotStart~ 104~ 62.8334~ 102.5 ~10JAN11 09:42:55~
5 ~7001-WAFER_SELECT~ 01-LotStart~ 105~ 63.5798~ 102.5 ~10JAN11 09:42:36~
6 ~7001-WAFER_SELECT~ 02-Clean~ 106~ 57.397~ 183.0 ~10JAN11 09:43:54~
7 ~7001-WAFER_SELECT~ 02-Clean~ 107~ 59.7878~ 384.5 ~10JAN11 09:44:01~
8 ~7001-WAFER_SELECT~ 02-Clean~ 108~ 62.5087~ 112.5 ~10JAN11 09:44:05~
9 ~7001-WAFER_SELECT~ 02-Clean~ 109~ 62.5789~ 184.0 ~10JAN11 10:30:22~
10 ~7001-WAFER_SELECT~ 02-Clean~ 110~ 59.0644~ 399.5 ~10JAN11 10:30:31~
11 ~7001-WAFER_SELECT~ 03-ComposeLot~ 111~ 51.34325~ 250.5 ~10JAN11 10:30:33~
12 ~7001-WAFER_SELECT~ 03-ComposeLot~ 112~ 64.314~ 290.0 ~10JAN11 10:30:34~
13 ~7001-WAFER_SELECT~ 03-ComposeLot~ 113~ 56.1343~ 277.0 ~10JAN11 10:30:35~
14 ~7001-WAFER_SELECT~ 03-ComposeLot~ 114~ 66.534~ 112.0 ~10JAN11 10:30:35~
15 ~7001-WAFER_SELECT~ 03-ComposeLot~ 115~ 72.0134~ 150.0 ~10JAN11 10:30:35~
;
run;

options linesize=256 nocenter;
ods listing;
ods html file='c:\temp\showorder.html' style=sasweb;
proc report data=stuff nowd;
title '1) notice appearance of operation with operation to the right of datim2';
title2 'OPERATION duplicate values are NOT suppressed because of uniqueness of DATIM2';
column ColorValueField datim datim2 operation step user_ID n1 n2 n3;
define ColorValueField /display 'CVF';
define datim / display f=datetime18.;
define datim2 / order f=datetime18. order=internal;
define operation / order 'OPER';
define step / order;
define user_ID / display;
define n1 / display;
define n2 / display;
define n3 / display;
run;

proc report data=stuff nowd;
title '2) notice appearance of operation with operation to the left of datim2';
title2 'OPERATION duplicate values ARE suppressed because OPERATION now appears BEFORE DATIM2 in COLUMN statement';
column ColorValueField datim operation datim2 step user_ID n1 n2 n3;
define ColorValueField /display 'CVF';
define datim / display f=datetime18.;
define operation / order 'OPER';
define datim2 / order f=datetime18. order=internal;
define step / order;
define user_ID / display;
define n1 / display;
define n2 / display;
define n3 / display;
run;

proc report data=stuff nowd;
title '3) notice appearance of operation and step with both vars to the left of datim2';
title2 'OPERATION and STEP duplicate values ARE suppressed because of var placement in COLUMN statement';
column ColorValueField datim operation step datim2 user_ID n1 n2 n3;
define ColorValueField /display 'CVF';
define datim / display f=datetime18.;
define operation / order 'OPER';
define step / order;
define datim2 / order f=datetime18. order=internal;
define user_ID / display;
define n1 / display;
define n2 / display;
define n3 / display;
run;

ods html close;
[/pre]