data test;
input ln $ step $ date1 date9.;
format date1 date9.;
datalines;
0122 B 01jun2019
0122 B 07jun2019
0122 C 06jun2019
0133 C 07jun2019
0133 B 01jun2019
0133 B 01jun2019
;
run;
The desired output is to get distinct row numbers based on the change in ln,step and date. Then reset with a new step or ln. Something like this;
ln | step | date1 | row_num |
0122 | B | 01Jun2019 | 1 |
0122 | B | 07Jun2019 | 2 |
0122 | C | 06Jun2019 | 1 |
0133 | C | 07Jun2019 | 2 |
0133 | B | 01Jun2019 | 1 |
0133 | B | 01Jun2019 | 1 |
You asked for "distinct row numbers based on the change in ln,step and date." but the result shows distinct row per ln step
only, in 0122 B 07jun2019 - why is row_num = 2 ?)
Any way, assuming data is sorted by ln step then:
data want;
set have;
by ln step;
retain row_num;
if first.step then row_num=1;
else row_num+1;
run;
Your explanations are unclear to me.
This matches your output:
data WANT;
set HAVE;
if lag(DATE1)>=DATE1 then ROW_NUM=0;
ROW_NUM+1;
run;
LN | STEP | DATE1 | ROW_NUM |
---|---|---|---|
0122 | B | 01JUN2019 | 1 |
0122 | B | 07JUN2019 | 2 |
0122 | C | 06JUN2019 | 1 |
0133 | C | 07JUN2019 | 2 |
0133 | B | 01JUN2019 | 1 |
0133 | B | 01JUN2019 | 1 |
It looks like ln has no role in determining row_num, so I commented it out:
proc rank data=test out=want ties=dense;
by /*ln*/ step notsorted;
var date1;
ranks row_num;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.