how to get data that has certain code onwards and check if they go backwards

[BaalaRaaji]

Hi Team,

I have just copy pasted the sample data that I need help in querying the output.

Below is the example of 2 accounts..(Real data has 50k accounts)..

1. I need to filter data to get the account list where the code starts from 'gg'

2. Then I need to create flag if from the code 'gg' if it goes backwards (aa or bb or cc or dd or ee or ff )

I have attached the sample data in note pad to export in sas and work and also pasted below for reference

sample data below:(same as notepad data)

accno	date	code
100	31-Jan-15	aa
100	28-Feb-15	bb
100	31-Mar-15	cc
100	30-Apr-15	dd
100	31-May-15	ee
100	30-Jun-15	ff
100	31-Jul-15	gg
100	30-Aug-15	aa
100	30-Sep-15	bb
100	30-Oct-15	hh
100	30-Nov-15	ii
100	31-Dec-15	jj
100	31-Jan-16	kk
100	28-Feb-16	ll
200	31-Jan-15	aa
200	28-Feb-15	bb
200	31-Mar-15	cc
200	30-Apr-15	dd
200	31-May-15	ee
200	30-Jun-15	ff
200	31-Jul-15	gg
200	30-Aug-15	hh
200	30-Sep-15	ii
200	30-Oct-15	jj
200	30-Nov-15	kk
200	31-Dec-15	ll
200	31-Jan-16	aa
200	28-Feb-16	bb

Output...

accno	date	code	Flag
100	31-Jul-15	gg
100	30-Aug-15	aa	add filter if it goes back wards
100	30-Sep-15	hh
100	30-Oct-15	ii
100	30-Nov-15	jj
100	31-Dec-15	kk
100	31-Jan-16	ll
100	28-Feb-16	mm
200	31-Jul-15	gg
200	30-Aug-15	hh
200	30-Sep-15	ii
200	30-Oct-15	jj
200	30-Nov-15	kk
200	31-Dec-15	ll
200	31-Jan-16	aa	add filter if it goes back wards
200	28-Feb-16	bb	add filter if it goes back wards

Thanks for the help.

Reagrds

BR

[Reeza]

Where does the 100 mm record come from? The last record in your 100 output?

Otherwise, I think this does what you need, at least it filters it, it doesn't add the flag. Not sure I understand the logic for that, except maybe it's out of alphabetical order?

data want;
set have;
by accno date;

retain flag;

if first.accno then flag=0;

if code = gg then flag=1;

if flag=1 then output; 

*not sure about this;
if code < 'gg' then filter='Y';

run;

---

@BaalaRaaji wrote:

Hi Team,

I have just copy pasted the sample data that I need help in querying the output.

Below is the example of 2 accounts..(Real data has 50k accounts)..

1. I need to filter data to get the account list where the code starts from 'gg'

2. Then I need to create flag if from the code 'gg' if it goes backwards (aa or bb or cc or dd or ee or ff )

I have attached the sample data in note pad to export in sas and work and also pasted below for reference

sample data below:(same as notepad data)

accno	date	code
100	31-Jan-15	aa
100	28-Feb-15	bb
100	31-Mar-15	cc
100	30-Apr-15	dd
100	31-May-15	ee
100	30-Jun-15	ff
100	31-Jul-15	gg
100	30-Aug-15	aa
100	30-Sep-15	bb
100	30-Oct-15	hh
100	30-Nov-15	ii
100	31-Dec-15	jj
100	31-Jan-16	kk
100	28-Feb-16	ll
200	31-Jan-15	aa
200	28-Feb-15	bb
200	31-Mar-15	cc
200	30-Apr-15	dd
200	31-May-15	ee
200	30-Jun-15	ff
200	31-Jul-15	gg
200	30-Aug-15	hh
200	30-Sep-15	ii
200	30-Oct-15	jj
200	30-Nov-15	kk
200	31-Dec-15	ll
200	31-Jan-16	aa
200	28-Feb-16	bb

Output...

accno	date	code	Flag
100	31-Jul-15	gg
100	30-Aug-15	aa	add filter if it goes back wards
100	30-Sep-15	hh
100	30-Oct-15	ii
100	30-Nov-15	jj
100	31-Dec-15	kk
100	31-Jan-16	ll
100	28-Feb-16	mm
200	31-Jul-15	gg
200	30-Aug-15	hh
200	30-Sep-15	ii
200	30-Oct-15	jj
200	30-Nov-15	kk
200	31-Dec-15	ll
200	31-Jan-16	aa	add filter if it goes back wards
200	28-Feb-16	bb	add filter if it goes back wards

Thanks for the help.

Reagrds

BR

---

[ChrisNZ]

Or maybe:

data WANT;
 set HAVE;
 by ACCNO ;
 if first.ACCNO then KEEP=0;
 if CODE = 'gg' then KEEP+1;
 if KEEP & CODE < lag(CODE) then FILTER='Y';
 if KEEP;
run;

[Reeza]

Yeah, not sure if the logic is less than gg or less than previous? And not sure if it's lexicographical as shown either.

[ChrisNZ]

Who knows? The supplied example isn't explicit on this point, and isn't consistent with itself either.