sas logic

[sfffdg]

Hi ,

The Data set A is below.

data A;
input p1 t1 r1 t2 d1 pre;
cards;
1 201 1 200 201901 10
1 201 1 200 201901 10
1 201 2 201 201901 5
2 202 1 202 201902 10
;
run;

Data set c is as below:

data c;
input p1 d2 d3 t4;
cards;
1 201812 201912 1
1 201812 201912 2
2 201901 202001 4
;
run;

proc sql;
Create table D as
Select a.*,b.d2, b.d3, b.t4
from A a left join C b
on a.p1=b.p1 and b.d2<=a.d1<b.d3;
quit;

proc sort data=D;
by p1 t1 r1 t4;
run;

The result of the above query is :

P1	T1	R1	T2	D1	PRE	D2	D3	T4
1	201	1	200	201901	10	201812	201912	1
1	201	1	200	201901	10	201812	201912	1
1	201	1	200	201901	10	201812	201912	2
1	201	1	200	201901	10	201812	201912	2
1	201	2	201	201901	5	201812	201912	1
1	201	2	201	201901	5	201812	201912	2
2	202	1	202	201902	10	201901	202001	4

How to get the below data set from 'D'

P1	T1	R1	T2	D1	PRE	D2	D3	T4
1	201	1	200	201901	10	201812	201912	1
1	201	1	200	201901	10	201812	201912	1
1	201	2	201	201901	5	201812	201912	1
2	202	1	202	201902	10	201901	202001	4

we basically need the data for the minimum number of t4.

Thanks a lot!

[AMSAS · Accepted solution]

Take a look at FIRST. and LAST. DATA Step Variables 

data e ;
	retain rt4 ; /* Keep the value of rt4 across iterations of the data step */
	set d ;
	by p1 t1 r1 t4;
	if first.r1 then rt4=t4 ; /* If it's the first occurance of r1 then store the value of t4 in rt4 */
	if rt4=t4 then output ; /* Output a record if rt4=t4 */
run ;