Selecting records based on date ranges which vary by user

[shuvaya]

Hi,

I have a dataset which looks like the below:

id	First_Qual_Month	Txn_Month
1000361	Jul-19	Jul-19
1000361	Jul-19	Aug-19
1000361	Jul-19	Sep-19
100897	Jan-20	Oct-19
100897	Jan-20	Nov-19
100897	Jan-20	Dec-19
100897	Jan-20	Jan-20
100897	Jan-20	Feb-20
100897	Jan-20	Mar-20

I need to select only those rows for each id where the months are after the first_qual_month. I can select upto next 6 months of data per user.

Can someone please help me on this? I am new to sas and first. doesn't look to be the solution!

[PeterClemmensen]

See if you can use this as a template.

Feel free to ask 🙂

options yearcutoff = 2000;

data have;
input id $ First_Qual_Month $ Txn_Month $;
datalines;
1000361 Jul-19 Jul-19
1000361 Jul-19 Aug-19
1000361 Jul-19 Sep-19
100897  Jan-20 Oct-19
100897  Jan-20 Nov-19
100897  Jan-20 Dec-19
100897  Jan-20 Jan-20
100897  Jan-20 Feb-20
100897  Jan-20 Mar-20
;

data want;
   do c = 1 by 1 until (last.id);
      set have;
      by id;
      if input(First_Qual_Month, monyy6.) < input(Txn_Month, monyy6.) then output;
      if c = 6 then leave;
   end;
run;

[andreas_lds]

As always: store dates as dates and you can use simple where-statements.

data have;
   length id $ 10 First_Qual_Month Txn_Month $ 6;
   input id First_Qual_Month Txn_Month;
   datalines;
1000361  Jul-19   Jul-19
1000361  Jul-19   Aug-19
1000361  Jul-19   Sep-19
100897   Jan-20   Oct-19
100897   Jan-20   Nov-19
100897   Jan-20   Dec-19
100897   Jan-20   Jan-20
100897   Jan-20   Feb-20
100897   Jan-20   Mar-20
;

data fixed_dates;
   set have;
   length FirstQualMonth TxnMonth 8;
   format FirstQualMonth TxnMonth mmyyd8.;
   
   FirstQualMonth = input(First_Qual_Month, monyy6.);
   TxnMonth = input(Txn_Month, monyy6.);
   
   drop First_Qual_Month Txn_Month;
   rename 
      FirstQualMonth = First_Qual_Month 
      TxnMonth = Txn_Month
   ;
run;

data want;
   set fixed_dates;
   where First_Qual_Month < Txn_Month;
run;

What should happen, if more than six obs have First_Qual_Month < Txn_Month?