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;
April 27 – 30 | Gaylord Texan | Grapevine, Texas
Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and save with the early bird rate—just $795!
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.