## Flag a record where sequence is broken

Solved
Frequent Contributor
Posts: 81

# Flag a record where sequence is broken

Hi,

I want to flag a record where sequence is broken (0=no, 1=yes)

data have;
input account sequence
datalines;
1 1
1 2
1 3
2 2
2 3
3 1
3 2
run;

Want
Account Flag
1 1
2 0
3 1

Accepted Solutions
Solution
‎06-07-2016 02:55 PM
Posts: 3,167

## Re: Flag a record where sequence is broken

If your 'sequence' always starts from '1', here is a SQL option:

``````data have;
input account sequence;
datalines;
1 1
1 2
1 3
2 2
2 3
3 1
3 2
;

proc sql;
create table want as
select account, (count(distinct sequence)=max(sequence)) as flag
from have
group by account
;
quit;``````

All Replies
Posts: 5,482

## Re: Flag a record where sequence is broken

[ Edited ]

Simple

``````data have;
input account sequence;
datalines;
1 1
1 2
1 3
2 2
2 3
3 1
3 2
;

data want;
flag = 1;
do seq = 1 by 1 until (last.account);
set have; by account;
if seq ne sequence then flag = 0;
end;
keep account flag;
run;

proc print; run;``````
PG
Super User
Posts: 6,632

## Re: Flag a record where sequence is broken

You'll have to explain the problem a little better.  None of these sequences look broken to me.  So why do you want 1, 0, 1 as the results?

Frequent Contributor
Posts: 81

## Re: Flag a record where sequence is broken

Thank you , I want the sequence starting froom 1,2,3 if it does not have a 1 that means the sequence is broken (2,3) is not in order of 1,2 3.

Super User
Posts: 6,632

## Re: Flag a record where sequence is broken

So does that mean that your "want" example data set is exactly the opposite of what you want?

Solution
‎06-07-2016 02:55 PM
Posts: 3,167

## Re: Flag a record where sequence is broken

If your 'sequence' always starts from '1', here is a SQL option:

``````data have;
input account sequence;
datalines;
1 1
1 2
1 3
2 2
2 3
3 1
3 2
;

proc sql;
create table want as
select account, (count(distinct sequence)=max(sequence)) as flag
from have
group by account
;
quit;``````
🔒 This topic is solved and locked.