BookmarkSubscribeRSS Feed
Bounce
Fluorite | Level 6

Hi all,

 

I would like to label each occurring event in an ascending order. So imagine if I have dataset with three columns: Dog (Name of Dog), Meal (Type of Meal) and Day (The day that the dog ate that meal).

 

Each of these dogs might have eaten a different meal on different days or even the same days.

 

From these three columns of data, I would like to create column X which labels each data row based on the chronological order of the Day of the event per Meal per Dog:

 

Dog      Meal               Day         X

A           Fish                1             1
A           Fish                2             2
A           Chicken          2             1
A           Lamb              3             1

B           Fish                1             1

B           Chicken          2             1

B           Chicken          3             2

B           Chicken          4             3

B           Steak              5             1
B           Fish                5              2            
B           Chicken          5              4

C           Fish                1              1

C           Steak              2             1

C           Steak              3             2

C           Steak              4             3

C           Fish                4             2

C           Lamb              5             1
C           Steak              6             4

 

Many thanks in advance ! 🙂

1 REPLY 1
PeterClemmensen
Tourmaline | Level 20
data have;
input Dog $ Meal $ 3-9 Day;
datalines;
A Fish    1
A Fish    2
A Chicken 2
A Lamb    3
B Fish    1
B Chicken 2
B Chicken 3
B Chicken 4
B Steak   5
B Fish    5
B Chicken 5
C Fish    1
C Steak   2
C Steak   3
C Steak   4
C Fish    4
C Lamb    5
C Steak   6
;

data want;
  if _n_=1 then do;
    dcl hash h();
    h.defineKey ("Dog", "Meal");
    h.defineData ("X");
    h.defineDone ();
  end;
 
  set have;

  if h.find() ne 0 then X = 1;
  else                  X + 1;

  h.replace();
run;

Result:

 

 

Dog  Meal     Day  X
A    Fish     1    1
A    Fish     2    2
A    Chicken  2    1
A    Lamb     3    1
B    Fish     1    1
B    Chicken  2    1
B    Chicken  3    2
B    Chicken  4    3
B    Steak    5    1
B    Fish     5    2
B    Chicken  5    4
C    Fish     1    1
C    Steak    2    1
C    Steak    3    2
C    Steak    4    3
C    Fish     4    2
C    Lamb     5    1
C    Steak    6    4

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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
  • 605 views
  • 0 likes
  • 2 in conversation