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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 513 views
  • 3 likes
  • 4 in conversation