topic Re: How to obtain the 1st sequence number in SAS Procedures

How to obtain the 1st sequence number

BETO — Sun, 16 Mar 2014 01:32:02 GMT

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

Re: How to obtain the 1st sequence number

PGStats — Sun, 16 Mar 2014 02:13:01 GMT

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;

drop done;

run;

Re: How to obtain the 1st sequence number

Haikuo — Sun, 16 Mar 2014 02:40:30 GMT

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

Re: How to obtain the 1st sequence number

Tom — Sun, 16 Mar 2014 03:38:30 GMT

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