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!
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 ;
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 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.