How to obtain the 1st sequence number

[BETO]

HI I have and issue on how to obtain the first sequence number base of another sequence # for example...

i Have one table with data that looks like this

id.         Date.      Seq no.

1a. 03/01/2014. 123

2a.   03/02/2014. 234

second table looks like this

id.      Date.        Seq no

1a.      03/01//2014.  122

1a.      03/01/2014.   121

1a.       03/04/2014   124

2a.       03/02/2014.    235

2a.        03/01/2014.   233

2a.         03/01/2014.   232

what I need is to match from the first sequence # to the one before from the 2nd table for example I would need from 1a  is seq 122 and for 2a i would need seq 233... Thanks for your assistance

[PGStats · Accepted solution]

This seems to work:

data a;

input id $ DateA :mmddyy10. SeqA;

format dateA yymmdd10.;

datalines;

1a. 03/01/2014 123

2a. 03/02/2014 234

;

data b;

input id $ DateB :mmddyy10. SeqB;

format dateB yymmdd10.;

datalines;

1a. 03/01/2014  122

1a. 03/01/2014  121

1a. 03/04/2014  124

2a. 03/02/2014  235

2a. 03/01/2014  233

2a. 03/01/2014  232

;

proc sort data=a; by id descending seqA; run;

proc sort data=b; by id descending seqB; run;

data match;

do until(last.id);

  merge a b(in=inb); by id;

  if inb and seqB < seqA and not done then do;

    output;

    done = 1;

    end;

  end;

drop done;

run;

PG

[Haikuo]

PROC SQL APPROACH，raw data stolen from PG

data a;

input id $ DateA :mmddyy10. SeqA;

format dateA yymmdd10.;

datalines;

1a. 03/01/2014 123

2a. 03/02/2014 234

;

data b;

input id $ DateB :mmddyy10. SeqB;

format dateB yymmdd10.;

datalines;

1a. 03/01/2014  122

1a. 03/01/2014  121

1a. 03/04/2014  124

2a. 03/02/2014  235

2a. 03/01/2014  233

2a. 03/01/2014  232

;

PROC SQL;

  CREATE TABLE WANT AS

    SELECT A.ID, DATEA, (SELECT MAX(SEQB) FROM B WHERE A.ID=B.ID AND B.SEQB < A.SEQA GROUP BY B.ID) AS SEQ

   FROM A;

QUIT;

Haikuo

[Tom]

Sounds like you want the MAX(seqno) that is less than the value from the other table.  Is that what mean by FIRST?