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
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
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
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
Sounds like you want the MAX(seqno) that is less than the value from the other table. Is that what mean by FIRST?
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.