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
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;
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;
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?
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.
So does that mean that your "want" example data set is exactly the opposite of what you want?
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.