🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 03-04-2022 06:43 AM
(481 views)
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!
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ;
1 REPLY 1
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ;