SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sfffdg
Obsidian | Level 7

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
AMSAS
SAS Super FREQ

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 ;

View solution in original post

1 REPLY 1
AMSAS
SAS Super FREQ

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 ;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 482 views
  • 1 like
  • 2 in conversation