## How to obtain the 1st sequence number

Solved
Regular Contributor
Posts: 240

# How to obtain the 1st sequence number

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

Accepted Solutions
Solution
‎03-15-2014 10:13 PM
Posts: 5,543

## Re: How to obtain the 1st sequence number

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

PG

All Replies
Solution
‎03-15-2014 10:13 PM
Posts: 5,543

## Re: How to obtain the 1st sequence number

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

PG
Posts: 3,167

## Re: How to obtain the 1st sequence number

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

Super User
Posts: 8,129

## Re: How to obtain the 1st sequence number

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

🔒 This topic is solved and locked.