DATA Step, Macro, Functions and more

Counting observations per ID numerically

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Counting observations per ID numerically

Hi Y'all,

 

Please be patient with my explanation as my request is a tad cumbersome.

 

I have data that looks like this:

 

ID

SEQNO

101

1

102

6

103

2

103

3

104

9

104

10

104

12

104

17

105

1

105

2

105

5

105

7

105

8

105

12

106

27

106

28

106

29

 

For every unique ID, I created the programming variable called "SEQNO". I cleaned the data, so the SEQNO values are no longer complete; however, they still go from smallest to largest value for each ID. What I need now is to do a count called NUM based on the following three conditions:

 

  • A unique ID may only have one observation, so it will only have one SEQNO value [please refer to IDs: 101, 102]. For this condition, NUM is 1.
  • A unique ID may have multiple observations, with the SEQNO values going in numerical order [please refer to IDs: 103, 106]. For this condition, NUM is 1, regardless of the number of observations.
  • A unique ID may have multiple observations, with the SEQNO values going in numerical order but also containing breaks in order [please refer to IDs: 104, 105]. For this condition, NUM starts as 1 but then adds 1 for every break it encounters.

 

(Quick note, SEQNO value does not always start at 1; however, the key point is the numerical order).

 

Here's one way of how the table I want may be:

 

ID

SEQNO

NUM

101

1

1

102

6

1

103

2

1

103

3

1

104

9

1

104

10

1

104

12

2

104

17

3

105

1

1

105

2

1

105

5

2

105

7

3

105

8

3

105

12

4

106

27

1

106

28

1

106

29

1

 

Even though in the table above, I kept every observation, I do not need every row. So an alternate table that I can use could be:

 

ID

SEQNO

NUM

101

1

1

102

6

1

103

3

1

104

17

3

105

12

4

106

29

1

 

(Do not worry about the SEQNO, it was just a programmatic variable meant to help me clean the data).

 

I am using SAS 9.4

 

Let me know if my request needs additional clarifications.

 

Muchas Gracias!


Accepted Solutions
Solution
‎11-17-2016 09:10 AM
Super User
Posts: 10,495

Re: Counting observations per ID numerically

[ Edited ]

Assuming your data is in the order shown then BY group processing does what you want. We can make both tables in a single pass.

If I understand the requirement:

 

data want1 want2;
   set have;
   by id;
   retain Num;
   DifSeq= dif(Seqno);
   if first.id then Num=1;
   else if DifSeq>1 then Num+1;
   if last.id then output want2;
   output want1;
   drop difseq;
run;

Or alternatively instead of separate output sets have a flag set for the second group instead of Output want1 such as:

 

Group2Flag=Last.id; and remove the want2 and If last.id ... statement.

View solution in original post


All Replies
Solution
‎11-17-2016 09:10 AM
Super User
Posts: 10,495

Re: Counting observations per ID numerically

[ Edited ]

Assuming your data is in the order shown then BY group processing does what you want. We can make both tables in a single pass.

If I understand the requirement:

 

data want1 want2;
   set have;
   by id;
   retain Num;
   DifSeq= dif(Seqno);
   if first.id then Num=1;
   else if DifSeq>1 then Num+1;
   if last.id then output want2;
   output want1;
   drop difseq;
run;

Or alternatively instead of separate output sets have a flag set for the second group instead of Output want1 such as:

 

Group2Flag=Last.id; and remove the want2 and If last.id ... statement.

Super User
Posts: 5,081

Re: Counting observations per ID numerically

That's a good approach.  A small point ... I think what you meant is:

 

else if DifSeq > 1 then ...

Super User
Posts: 10,495

Re: Counting observations per ID numerically


Astounding wrote:

That's a good approach.  A small point ... I think what you meant is:

 

else if DifSeq > 1 then ...


 

Good catch!.

Contributor
Posts: 45

Re: Counting observations per ID numerically

Thanks for the help you two! I was thinking of the "By" function but wasn't sure how to handle the breaks in numerical order. Your code makes sense and best of all, it works! Smiley Happy

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 351 views
  • 5 likes
  • 3 in conversation