topic Re: how to get data that has certain code onwards and check if they go backwards in SAS Programming

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

BaalaRaaji — Fri, 24 May 2019 01:01:15 GMT

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

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

Reeza — Fri, 24 May 2019 02:46:20 GMT

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

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

ChrisNZ — Fri, 24 May 2019 02:54:27 GMT

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;

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

Reeza — Fri, 24 May 2019 02:55:45 GMT

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

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

ChrisNZ — Fri, 24 May 2019 02:57:13 GMT

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