topic Re: Flag a record where sequence is broken in SAS Data Management

Flag a record where sequence is broken

AZIQ1 — Mon, 23 May 2016 18:45:09 GMT

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

Re: Flag a record where sequence is broken

PGStats — Mon, 23 May 2016 19:36:03 GMT

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;

Re: Flag a record where sequence is broken

Astounding — Mon, 23 May 2016 19:17:58 GMT

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?

Re: Flag a record where sequence is broken

AZIQ1 — Mon, 23 May 2016 19:33:46 GMT

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.

Re: Flag a record where sequence is broken

Astounding — Mon, 23 May 2016 19:52:25 GMT

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

Re: Flag a record where sequence is broken

Haikuo — Mon, 23 May 2016 20:44:12 GMT

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;