The DEFINE is 'TRUE_DATE/MDYY= ' it should capture the EVNT_DATE of the first occurrence of 'C'in the EVNT field
and if there is no 'C' then capture the EVNT_DATE of 'P'
I've tried the code below in many ways but I can't get it to work. I realized that it will only look at the EVNT
field one row at a time. How do I get it to look at all rows with same ID.

DEFINE
TRUE_DATE/MDYY=IF EVNT EQ 'C' THEN EVNT_DATE
ELSE IF EVNT NE 'C' AND EVENT EQ 'P' THE EVNT_DATE ELSE ' ';
END

The define doesn't work; it Populates TRUE_DATE for all EVNT EQ 'C' AND 'P'. I understand why it doesn't work but I
don't know what will make it work.

The output should produce the following results:
ID ITM EVNT SEQ EVNT_DATE TRUE_DATE
57 BLT P 1 09/30/2014
57 BLT C 2 09/30/2014 09/30/2014
57 BLT I 3 10/08/2014
57 BLT C 4 10/24/2014

60 BLT P 1 09/03/2014 09/03/2014
60 BLT I 3 10/08/2014

This message has been edited. Last edited by: Starr,

quote:

09/03/2014

DEFINE
TRUE_DATE/MDYY=IF EVNT EQ 'C' THEN EVNT_DATE
ELSE IF EVNT NE 'C' AND EVENT EQ 'P' THE EVNT_DATE ELSE ' ';
END

------------------
In Your code , There is a IF - ELSE syntax error.

Post your condition, what you needed.

Sorry it was a typo but I do have the THEN in the actual code. I'm not getting an error just wrong results.

For ID 57 there are 4 rows. I want the define to place a date in only 1 row IF the following is true. If ID 57 has an EVNT EQ 'C' then populate TRUE_DATE with EVNT_DATE, the 'C' must be the first occurrence this can be determined by the SEQ field, then quit looking. Else IF there is no 'C' then look for 'P';

Does this require a match or some sort of loop?

I tried posting the desired result set. Hopefully that doesn't confuse you more.

Don't know if it's what you're looking for, but take a look. At least it will be s starting point.

DEFINE FILE CAR
FND_SEDAN/A15 = IF COUNTRY EQ LAST COUNTRY THEN (IF BODYTYPE EQ 'SEDAN' THEN 'SEDAN FND' ELSE IF BODYTYPE EQ 'COUPE' THEN 'NOT FND' ELSE LAST FND_SEDAN)
               ELSE (IF BODYTYPE EQ 'SEDAN' THEN 'SEDAN FND' ELSE IF BODYTYPE EQ 'COUPE' THEN 'NOT FND' ELSE '');
END
TABLE FILE CAR
PRINT
   BODYTYPE
   FND_SEDAN
BY COUNTRY
END

It helps to mark output with CODE tags...

You want to produce the following results:

ID  ITM EVNT SEQ EVNT_DATE   TRUE_DATE
57  BLT P    1   09/30/2014
57  BLT C    2   09/30/2014  09/30/2014
57  BLT I    3   10/08/2014
57  BLT C    4   10/24/2014

60  BLT P    1   09/03/2014  09/03/2014
60  BLT I    3   10/08/2014

From your spec, you need to consider all C event rows before the P event rows, within each ID, in order to identify the "triggering" C or P row.
Define won't be of much use, since it considers the data rows in the order presented.

I assume that SEQ >0 and is unique within ID. Then the following should pin the correct TRUE_DATE value for each ID group to the triggering row, and leave it blank on the other rows.

TABLE FILE ...
PRINT
  ITM 
  EVNT 
  SEQ 
  EVNT_DATE  
  COMPUTE
    MARK_SEQ/I1 = 
      IF ( ID NE LAST ID ) AND ( LAST MARK_SEQ GT 0 ) THEN LAST_MARK_SEQ
      ELSE IF ( EVNT IN ('C','P') ) THEN SEQ 
      ELSE 0;
    TRUE_DATE/MDYY = IF MARK_SEQ EQ SEQ THEN EVNT_DATE ELSE ' ';

  BY ID SKIP-LINE
  BY TOTAL SEQ NOPRINT
  BY EVNT NOPRINT
  BY SEQ NOPRINT
END

I first sort the incoming rows by ID, EVNT (to place C's before P's), SEQ. The BY TOTAL SEQ rearranges the output into ID, SEQ order after the COMPUTEs have been processed.

This message has been edited. Last edited by: j.gross,

j.gross I used the sample you posted and I'm almost there. The problem is the C and P and sometimes have the same EVNT_DATE so when that happens I get dups. Therefore MARK_SEQ EQ SEQ and both rows are captured. I've been trying to get around it for last two hours. What can I do to make it choose the C/(make it populate TRUE_DATE for the C row)?

My last post may have been confusing.....let me try again.

j.gross, I used the sample you posted. The problem is.... there are IDs with both C and P records P is SEQ 1 C is SEQ 2. They are both being marked therefore they are both getting a TRUE_DATE. How can I only get a TRUE_DATE for C when this happens?

BY ID SKIP-LINE
BY HIGHEST 1 SEQ NOPRINT
BY EVNT NOPRINT
BY SEQ NOPRINT

I find it easier to make two passes of the data. The first pass determines the TRUE_DATE and related ID/SEQ values.

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
-* Write data to temp file
FILEDEF STARR_DATA DISK STARR_DATA.FTM
-RUN
-WRITE STARR_DATA 57BLTP 109/30/2014
-WRITE STARR_DATA 57BLTC 209/30/2014
-WRITE STARR_DATA 57BLTI 310/08/2014
-WRITE STARR_DATA 57BLTC 410/24/2014
-WRITE STARR_DATA 60BLTP 109/03/2014
-WRITE STARR_DATA 60BLTI 310/08/2014
-RUN
-* Create master to read temp data file.
FILEDEF MAST DISK STARR_DATA.MAS
-RUN
ID  ITM EVNT SEQ EVNT_DATE   TRUE_DATE
-WRITE MAST FILENAME=STARR_DATA, SUFFIX=FIX
-WRITE MAST  SEGNAME=SEG01, SEGTYPE=S0, $
-WRITE MAST   FIELDNAME=ID       ,, ACTUAL=A2 , USAGE=A2, $
-WRITE MAST   FIELDNAME=ITM      ,, ACTUAL=A3 , USAGE=A3, $
-WRITE MAST   FIELDNAME=EVNT     ,, ACTUAL=A1 , USAGE=A1, $
-WRITE MAST   FIELDNAME=SEQ      ,, ACTUAL=A2 , USAGE=I2, $
-WRITE MAST   FIELDNAME=EVNT_DATE,, ACTUAL=A10, USAGE=MDYY, $
-RUN
-* Determine TRUE_DATE for each ID value. Hold results in indexed FOCUS file.
TABLE FILE STARR_DATA
 SUM MIN.EVNT_DATE AS 'TRUE_DATE'
 BY ID
 BY LOWEST 1 EVNT
 BY LOWEST 1 SEQ
 WHERE (EVNT EQ 'C' OR 'P');
 WHERE (EVNT_DATE NE '');
 ON TABLE HOLD AS TRUE_DATES FORMAT FOCUS INDEX ID
END
-* Join original data to TRUE_DATE results and produce final report.
JOIN CLEAR *
JOIN ID IN STARR_DATA TO UNIQUE ID IN TRUE_DATES AS J1
-*
DEFINE FILE STARR_DATA
 TRUE_DATEX/MDYY = IF (STARR_DATA.SEQ EQ TRUE_DATES.SEQ) THEN TRUE_DATES.TRUE_DATE ELSE '';
END
-*
TABLE FILE STARR_DATA
 PRINT
  STARR_DATA.ID
  STARR_DATA.ITM
  STARR_DATA.EVNT
  STARR_DATA.SEQ
  STARR_DATA.EVNT_DATE
  TRUE_DATEX
 BY STARR_DATA.ID  NOPRINT
 BY STARR_DATA.SEQ NOPRINT
END

EDIT: Added BY LOWEST 1 SEQ.

This message has been edited. Last edited by: Dan Satchell,

I tried that earlier and it eliminated most of my output. It seems to need the BY TOTAL SEQ NOPRINT. I put the HIGHEST on the second BY HIGHEST SEQ.

BY TOTAL SEQ NOPRINT
BY EVNT NOPRINT
BY HIGHEST 1 SEQ NOPRINT

Still not working.

Thanks Dan. I knew this would result in my using Dialogue Manager. I guess I better learn it. OK I'll try to write this....I wish my data was as simple as the sample I put out there. I'll be checking in.

Hi Starr,
Dan has a given a good option, might try that.
I missed that you wanted both records...
Tom

@ Starr: I only used Dialogue Manger to create the test data file and master. It won't be needed for the actual solution. The only part of my post you should need starts with the first TABLE FILE command.

Thanks...I realized that after I read the entire post. Will take a stab at it now.

@ Starr: I made a change to my original post, indicated by the word EDIT at the bottom.

quote:

I used the sample you posted and I'm almost there. The problem is the C and P and sometimes have the same EVNT_DATE so when that happens I get dups. Therefore MARK_SEQ EQ SEQ and both rows are captured.

My bad. Change ID NE ... to ID EQ ... :

MARK_SEQ/I1 = 
      IF ( ID EQ LAST ID ) AND ( LAST MARK_SEQ GT 0 ) THEN LAST_MARK_SEQ
      ELSE IF ( EVNT IN ('C','P') ) THEN SEQ 
      ELSE 0;

This message has been edited. Last edited by: j.gross,

Thanks to everyone for all your help. My issue is resolved and I learned a few things.

This forum is the best.