Help using Base SAS procedures

How to obtain the 1st sequence number

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

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
Respected Advisor
Posts: 4,649

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

View solution in original post


All Replies
Solution
‎03-15-2014 10:13 PM
Respected Advisor
Posts: 4,649

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
Respected Advisor
Posts: 3,124

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
Super User
Posts: 6,500

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 164 views
  • 3 likes
  • 4 in conversation