Flag a record where sequence is broken

[AZIQ1]

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

[PGStats]

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;

[Astounding]

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?

[AZIQ1]

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.

[Astounding]

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

[Haikuo · Accepted solution]

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;