I was hoping if some can help me figure out how to append a row to the previous row or point me in the right direction on how to do this. I have a table with three columns (Product Number, Effective Product Number and Effective Date) and if Product Number is the same as the next Product Number then append that row next to the previous row.

Below is an example.

This is what the table looks like:

PRD_N___EFF_PRD_N____EFF_DT
3_______3____________08/16/2012
3_______121__________11/01/2012
3_______48___________06/01/2013
424_____21___________08/16/2012
424_____2____________12/01/2012
424_____21___________07/19/2013
and so on.......

This is what I want it to look like:

PRD_N___EFF_PRD_N____EFF_DT_______NEXT_EFF_PRD_N______NEXT_EFF_DT
3_______3____________08/16/2012___121_________________11/01/2012
3_______121__________11/01/2012___48__________________06/01/2013
3_______48___________06/01/2013___
424_____21___________08/16/2012___2___________________12/01/2012
424_____2____________12/01/2012___21__________________07/19/2013
424_____21___________07/19/2013___

Thanks,
Mike

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

If you sort in the reverse order, then use the LAST command to pick up the previous value.

Or maybe something like this:

TABLE FILE CAR
 PRINT
 COMPUTE NEW_COUNTRY/A10 = LAST COUNTRY ;
 COMPUTE NEW_CAR/A16     = LAST CAR ;
 COMPUTE NEW_MODEL/A24   = LAST MODEL ;
 COMPUTE NEXT_CAR/A16    = IF (NEW_COUNTRY EQ COUNTRY) THEN CAR ELSE '';
 COMPUTE NEXT_MODEL/A24  = IF (NEW_COUNTRY EQ COUNTRY) THEN MODEL ELSE '';
 BY COUNTRY NOPRINT
 BY CAR     NOPRINT
 WHERE TOTAL NEW_COUNTRY NE '';
END

or

TABLE FILE IBISAMP/CAR
  SUM COMPUTE itemnumber/I2=IF LAST itemnumber EQ 0 THEN 2 ELSE LAST itemnumber + 1 ; BY CAR
ON TABLE HOLD AS FIRST 
END
TABLE FILE IBISAMP/CAR
SUM COMPUTE itemnumber/I2 =  LAST itemnumber + 1 ;
BY CAR AS CAR2
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS SECOND
END
JOIN itemnumber IN FIRST TO itemnumber IN SECOND AS J1
TABLE FILE FIRST
PRINT CAR CAR2 BY itemnumber
END

Thank you suzannah and Dan Satchell for the suggestion.

suzannah,
I am trying your approach but I am having trouble with when the product number changes then dont append row.

Below is what I am getting when I use your code:
PRD_N___EFF_PRD_N____EFF_DT_______NEXT_EFF_PRD_N______NEXT_EFF_DT
3_______3____________08/16/2012___121_________________11/01/2012
3_______121__________11/01/2012___48__________________06/01/2013
3_______48___________06/01/2013___21__________________08/16/2012
424_____21___________08/16/2012___2___________________12/01/2012
424_____2____________12/01/2012___21__________________07/19/2013
424_____21___________07/19/2013___

I need it to look like below:
PRD_N___EFF_PRD_N____EFF_DT_______NEXT_EFF_PRD_N______NEXT_EFF_DT
3_______3____________08/16/2012___121_________________11/01/2012
3_______121__________11/01/2012___48__________________06/01/2013
3_______48___________06/01/2013___
424_____21___________08/16/2012___2___________________12/01/2012
424_____2____________12/01/2012___21__________________07/19/2013
424_____21___________07/19/2013___


Below is my code:

 
TABLE FILE GWM0099A
SUM
COMPUTE itemnumber/I2=IF LAST itemnumber EQ 0 THEN 2 ELSE LAST itemnumber + 1 ; 
BY PRD_N
BY PARNT_EFF_T NOPRINT
BY PARNT_PRD_N 
BY PARNT_EFF_T 
ON TABLE HOLD AS FIRST 
END

TABLE FILE GWM0099A
SUM COMPUTE itemnumber/I2 =  LAST itemnumber + 1 ;
BY PRD_N AS PRD_N2
BY PARNT_EFF_T NOPRINT
BY PARNT_PRD_N AS PARNT_PRD_N2
BY PARNT_EFF_T AS PARNT_EFF_T2
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS SECOND
END

JOIN itemnumber IN FIRST TO itemnumber IN SECOND AS J1

TABLE FILE FIRST
PRINT 
PRD_N 
PARNT_PRD_N
PARNT_EFF_T
PARNT_PRD_N2
PARNT_EFF_T2
BY itemnumber
END

look at each one of your files individually, before you join them, to see if its gunna work.

Another possible method (just to confuse things ) is to utilise the output from LIST and reprocess to achieve what Waz was suggesting (or at least what I think Waz was suggesting )

-* File rigugrid.fex
-* this first bit is just to recreate your sample data
FILEDEF RIGUMAS DISK RIGUDAT.MAS
-RUN
-WRITE RIGUMAS
-WRITE RIGUMAS FILENAME=RIGUDAT, SUFFIX=FOC
-WRITE RIGUMAS SEGNAME=SEG01, SEGTYPE=S1
-WRITE RIGUMAS FIELD=PRD_N,     ALIAS=PRD_N,     USAGE=I08, ACTUAL=I08, $
-WRITE RIGUMAS FIELD=EFF_PRD_N, ALIAS=EFF_PRD_N, USAGE=I08, ACTUAL=I08, $
-WRITE RIGUMAS FIELD=EFF_DT,    ALIAS=EFF_DT,    USAGE=MDYY, ACTUAL=MDYY, $
CREATE FILE RIGUDAT
MODIFY FILE RIGUDAT
FREEFORM PRD_N EFF_PRD_N EFF_DT
DATA
3,3,08/16/2012,$
3,121,11/01/2012,$
3,48,06/01/2013,$
424,21,08/16/2012,$
424,2,12/01/2012,$
424,21,07/19/2013,$
END
-* OK data recreated, let's have some fun.
TABLEF FILE RIGUDAT
 LIST EFF_PRD_N
      EFF_DT
   BY PRD_N
ON TABLE HOLD AS RIGUHOLD
END
-RUN
-* If you look at the output from the following command,
-* you will understand why I reference E02 :)
CMD TYPE RIGUHOLD.MAS
TABLE FILE RIGUHOLD
PRINT EFF_PRD_N
      EFF_DT
      COMPUTE NXT_EFF_PRD_N/I8 MISSING ON = IF PRD_N EQ LAST PRD_N THEN LAST EFF_PRD_N ELSE MISSING;
      COMPUTE NXT_EFF_DT/MDYY  MISSING ON = IF PRD_N EQ LAST PRD_N THEN LAST EFF_DT    ELSE MISSING;
   BY PRD_N
   BY HIGHEST E02 
ON TABLE HOLD AS RIGUHLD1
END
-RUN
TABLE FILE RIGUHLD1
PRINT EFF_PRD_N
      EFF_DT
      NXT_EFF_PRD_N
      NXT_EFF_DT
   BY PRD_N
   BY E02 NOPRINT
END

Having given you that, the method that Susannah has suggested to you is neat and you are almost there - you just need to work on what you are doing in your two extracts.

T

there are so many ways to do this...i think we should have a contest... EVERYBODY post a way...
lets see how many we can get...

Please ignore my first post - it fails to include the final record in the output. Here's something that does seem to work, based on Waz's suggestion, and using Tony's code to build the test data.

FILEDEF RIGUMAS DISK RIGUDAT.MAS
-RUN
-*
-WRITE RIGUMAS
-WRITE RIGUMAS FILENAME=RIGUDAT, SUFFIX=FOC
-WRITE RIGUMAS SEGNAME=SEG01, SEGTYPE=S1
-WRITE RIGUMAS FIELD=PRD_N,     ALIAS=PRD_N,     USAGE=I08, ACTUAL=I08, $
-WRITE RIGUMAS FIELD=EFF_PRD_N, ALIAS=EFF_PRD_N, USAGE=I08, ACTUAL=I08, $
-WRITE RIGUMAS FIELD=EFF_DT,    ALIAS=EFF_DT,    USAGE=MDYY, ACTUAL=MDYY, $
-*
CREATE FILE RIGUDAT
-*
MODIFY FILE RIGUDAT
 FREEFORM PRD_N EFF_PRD_N EFF_DT
 DATA
 3,3,08/16/2012,$
 3,121,11/01/2012,$
 3,48,06/01/2013,$
 424,21,08/16/2012,$
 424,2,12/01/2012,$
 424,21,07/19/2013,$
END
-*
TABLE FILE RIGUDAT
 PRINT EFF_PRD_N
 BY HIGHEST PRD_N
 BY HIGHEST EFF_DT
 ON TABLE HOLD AS REVERSE
END
-*
DEFINE FILE REVERSE
 NEXT_EFF_PRD_N/I8S = IF (LAST EFF_DT EQ '') OR (LAST PRD_N NE PRD_N) THEN 0  ELSE LAST EFF_PRD_N ;
 NEXT_EFF_DT/MDYY   = IF (LAST EFF_DT EQ '') OR (LAST PRD_N NE PRD_N) THEN '' ELSE LAST EFF_DT ;
END
-*
TABLE FILE REVERSE
 PRINT
  PRD_N
  EFF_PRD_N
  EFF_DT
  NEXT_EFF_PRD_N
  NEXT_EFF_DT
 BY PRD_N   NOPRINT
 BY EFF_DT  NOPRINT
END

Thank you all for the suggestions!!!

Tony A and Dan Satchell, your codes worked perfectly!!!

quote:

your codes worked perfectly!!!

As does Susannah's if you fill in with her implications -

TABLE FILE RIGUDAT
  SUM COMPUTE itemnumber/I2 = IF LAST itemnumber EQ 0 THEN 2 ELSE LAST itemnumber + 1 ;
   BY PRD_N
   BY EFF_DT
   BY EFF_PRD_N
ON TABLE HOLD AS FIRST 
END
TABLE FILE RIGUDAT
  SUM COMPUTE itemnumber/I2 =  LAST itemnumber + 1 ;
   BY PRD_N      AS PRD_N2
   BY EFF_DT     AS EFF_DT2
   BY EFF_PRD_N  AS EFF_PRD_N2
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS SECOND
END
JOIN itemnumber IN FIRST TO itemnumber IN SECOND AS J1
TABLE FILE FIRST
PRINT EFF_PRD_N
      EFF_DT
      COMPUTE NEXT_EFF_PRD_N/I8 MISSING ON = IF PRD_N EQ PRD_N2 THEN EFF_PRD_N2 ELSE MISSING;
      COMPUTE NEXT_EFF_DT/MDYY  MISSING ON = IF PRD_N EQ PRD_N2 THEN EFF_DT2    ELSE MISSING;
   BY itemnumber NOPRINT
   BY PRD_N
ON TABLE SET BYDISPLAY ON
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END

quote:

what Waz was suggesting (or at least what I think Waz was suggesting

Yes Tony, that was what I was suggesting.

Thanks for doing the coding for me.