Counting sub-sets of records in a data set

Accepted Solution Solved
Reply
Regular Contributor
Posts: 216
Accepted Solution

Counting sub-sets of records in a data set

Hi

I have a data set where I want to add a counter variable that indicates the record number for the records that belong to a given ID, as in the below example. I would like to use the Lag function and just add one to the previous value (if not the first record), but since you cannot use lag conditionally, I don't know how to make this work. I tried the below, but this just indicates a '1' for the first record and nothing else. If anyone knows of a way to use lag or something else for this, please let me know.

Paul

ID    Event     Date           Counter

1     EFV                        0

1     GTR     01/02/12      1

1     HYT     04/02/12      2

2     EFV                       0

2     GTR     01/02/12     1

2     HYT     04/02/12     2

2     YKR    05/03/12     3

data test2;

length Counter 3;

  set test1;

if missing(Date) then Counter=0;

Counter=lag(Counter)+1;

run;


Accepted Solutions
Solution
‎05-10-2013 06:07 PM
PROC Star
Posts: 7,492

Re: Counting sub-sets of records in a data set

Paul: You are going to get the wrong result unless you change the order of your statements.  Specifically, I would change your code to:

data test2;

  length Counter 3;

  set test1;

  by id;

  if first.id then Counter=0;

  Counter+1;

run;

View solution in original post


All Replies
Super User
Posts: 19,865

Re: Counting sub-sets of records in a data set

Regular Contributor
Posts: 216

Re: Counting sub-sets of records in a data set

Actually, I just tried this and it works:

data test2;

length Counter 3;

  set test1;

Counter+1;

if first.id then Counter=0;

run;

Super User
Posts: 19,865

Re: Counting sub-sets of records in a data set

If you don't have a BY statement first.id won't ever reset it and counter+1 is an implicit retain.

Regular Contributor
Posts: 216

Re: Counting sub-sets of records in a data set

Yes, the By statement is in the actual code. I forgot it in the above example though. Thanks.

Paul

Solution
‎05-10-2013 06:07 PM
PROC Star
Posts: 7,492

Re: Counting sub-sets of records in a data set

Paul: You are going to get the wrong result unless you change the order of your statements.  Specifically, I would change your code to:

data test2;

  length Counter 3;

  set test1;

  by id;

  if first.id then Counter=0;

  Counter+1;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 240 views
  • 3 likes
  • 3 in conversation