BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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

 

 

3 REPLIES 3
Shmuel
Garnet | Level 18

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;

 

ChrisNZ
Tourmaline | Level 20

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

 

PGStats
Opal | Level 21

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;
PG
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
  • 3 replies
  • 1166 views
  • 3 likes
  • 4 in conversation