Solved
Contributor
Posts: 45

# 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: 13,941

## 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.

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

## 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: 6,934

## 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: 13,941

## 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!

☑ This topic is solved.