SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Flag a record where sequence is broken

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

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
Respected Advisor
Posts: 3,156

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,937

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: 5,518

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?

Contributor
Posts: 71

Re: Flag a record where sequence is broken

Posted in reply to Astounding

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: 5,518

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
Respected Advisor
Posts: 3,156

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.

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

Discussion stats
  • 5 replies
  • 381 views
  • 0 likes
  • 4 in conversation