BookmarkSubscribeRSS Feed
shuvaya
Calcite | Level 5

Hi,

 

I have a dataset which looks like the below:

idFirst_Qual_MonthTxn_Month
1000361Jul-19Jul-19
1000361Jul-19Aug-19
1000361Jul-19Sep-19
100897Jan-20Oct-19
100897Jan-20Nov-19
100897Jan-20Dec-19
100897Jan-20Jan-20
100897Jan-20Feb-20
100897Jan-20Mar-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!

2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20

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
Jade | Level 19

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?

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 279 views
  • 0 likes
  • 3 in conversation