topic Re: I want to get a count for ID number that is 7 months apart from the Start date. in SAS Programming

I want to get a count for ID number that is 7 months apart from the Start date.

vtan9999 — Mon, 21 Mar 2016 21:15:49 GMT

I want to get a count for ID that is 7 apart from the initial date.

ID	Date	Payment
A123	Jan-12	250
A123	Mar-12	25
A123	Oct-12	350
A123	Dec-12	300
A123	Jan-13	450
A123	Dec-13	650
A123	Feb-14	300
A123	Mar-14	300
A123	Apr-14	300
A123	May-14	300
A123	Jun-14	300
A123	Jul-14	300
A123	Aug-14	300
A123	Sep-14	300
A124	Mar-12	300
A124	Jun-12	25
A124	Sep-12	30
A124	Dec-12	63
A124	Mar-13	15
A124	Jun-13	30
A124	Sep-13	21

Here is what I want

ID	Initial Date	Payment	New Date	Month Cnt	Cnt
A123	Jan-12	250	Jan-12		1
A123	Mar-12	25	Jan-12	-	0
A123	Oct-12	350	Oct-12	9	1
A123	Dec-12	300	Oct-12	-	0
A123	May-13	450	May-13	7	1
A123	Dec-13	650	Dec-13	7	1
A123	Feb-14	300	Dec-13	-	0
A123	Mar-14	300	Dec-13	-	0
A123	Apr-14	300	Dec-13	-	0
A123	May-14	300	Dec-13	-	0
A123	Jun-14	300	Dec-13	-	0
A123	Jul-14	300	Jul-14	7	1
A123	Aug-14	300	Jul-14	-	0
A123	Sep-14	300	Jul-14	-	0
A124	Mar-12	300	Mar-12		1
A124	Jun-12	25	Mar-12	-	0
A124	Sep-12	30	Mar-12	-	0
A124	Dec-12	63	Dec-12	9	1
A124	Mar-13	15	Dec-12	-	0
A124	Jul-13	30	Jul-13	7	1
A124	Sep-13	21	Jul-13	-	0


Summary	Total Payment	Total Count
A123	4,425	5
A124	484	3

Re: I want to get a count for ID number that is 7 months apart from the Start date.

RW9 — Tue, 22 Mar 2016 09:05:44 GMT

Hi,

Can I sugesst that you post some test data, in the form of a datastep, so that we have something to work with. I do not have time to type all that in. Secondly your logic does not seem to be clear:

ID	Initial Date	Payment	New Date	Month Cnt	Cnt
A123	Jan-12	250	Jan-12		1
A123	Mar-12	25	Jan-12	-	0
A123	Oct-12	350	Oct-12

What is new date, how is it derived? What is "start date". Why is jan12 counted as 1, but mar12 is not and then oct12 is, there appears to be no pattern. Generally speaking your logic would break down into three steps:

1) Ascertain your base date

2) Merge that onto your data, creating a field for months different

3) Counting where months <= 7

Re: I want to get a count for ID number that is 7 months apart from the Start date.

vtan9999 — Tue, 22 Mar 2016 16:05:02 GMT

RW9 - THANKS YOUR INPUT.

BELOW IS THE SAMPLE DATA AND CODE I TRY OUT.

data sample;

input id$4. DATE PAYMENT ;

attrib ID format =$4. informat=$4.;

attrib DATE format =date9. informat=date9.;

attrib PAYMENT format =9.0 informat=9.0;

datalines;

A123 01JAN2012 250

A123 01MAR2012 25

A123 01OCT2012 350

A123 01DEC2012 300

A123 01JAN2013 450

A123 01DEC2013 650

A123 01DEC2013 30

A123 01DEC2013 50

A123 01FEB2014 300

A123 01MAR2015 650

A124 01DEC2012 250

A124 01MAR2013 25

A124 01OCT2013 350

A124 01DEC2013 300

A124 01JAN2014 450

A124 01DEC2014 650

A124 01DEC2014 30

A124 01DEC2014 50

A124 01FEB2015 300

A124 01MAR2015 650

A125 01DEC2012 250

A125 01MAR2013 25

A125 01OCT2013 350

A125 01DEC2013 300

A125 01JAN2014 450

A125 01DEC2014 650

A125 01DEC2014 30

A125 01DEC2014 50

A125 01FEB2015 300

A125 01MAR2015 650

;

run;

proc sort data=sample; by ID Date; run;

Data TEST;

set sample;

by ID;

*retain Date;

if first.ID then Do;

NewDate=date;

Cnt=1;

end; return;

format NewDate date9.;

run;

I AM INTERESTING TO GET A COUNT FOR EVERY ID THAT ARE 7 MONHTS PART FROM THE PRIOR DATE IF THEY ARE NOT 7 MONTH APART. THE DEFAULT COUNT IS 1 AND NEWDATE IS INITIAL DATE EVERY FIRST ID AND DATE.

EX. THE FIRST ID A123 AND DATE 01JAN2012, THE COUNT =1 AND NEWDATE = 01JAN2012. AFTER THAT I WANT TO FILL THE NEWDATE AND COUNT IF THEY ARE 7 MONTH APART. IF THE DATE IS NOT 7 MONTHS PART, THEN KEEP THE NEWDATE THE SAME. EX: (01MAR2012-01JAN2012)/365.25*12 NOT GREATER OR EQUAL TO 7 THEN NEWDATE=01JAN2012, COUNT=0. THEN MOVE TO THE NEXT ONE (01OCT2012-01JAN2012)/365.25*12 > 7, NEWDATE=01OCT12, COUNT=1 AND SO ON. NEXT (01DEC2012-01OCT2012)/365.25*12<7, NEWDATE=01OCT12, COUNT=0 AND SO ON. IT IS SO COMPLECATED. SO THE END RESULT I WANT IS LOOK LIKE THIS

id	DATE	PAYMENT	NewDate	Cnt
A123	1/1/2012	250	1/1/2012	1
A123	3/1/2012	25	1/1/2012	0
A123	10/1/2012	350	10/1/2012	1
A123	12/1/2012	300	10/1/2012	0
A123	1/1/2013	450	10/1/2012	0
A123	12/1/2013	650	12/1/2013	1
A123	12/1/2013	30	12/1/2013	0
A123	12/1/2013	50	12/1/2013	0
A123	2/1/2014	300	12/1/2013	0
A123	3/1/2015	650	3/1/2015	1
A124	12/1/2012	250	12/1/2012	1
A124	3/1/2013	25	12/1/2012
A124	10/1/2013	350	10/1/2013	1
A124	12/1/2013	300	10/1/2013	0
A124	1/1/2014	450	10/1/2013	0
A124	12/1/2014	650	12/1/2014	1
A124	12/1/2014	30	12/1/2014	0
A124	12/1/2014	50	12/1/2014	0
A124	2/1/2015	300	12/1/2014	0
A124	3/1/2015	650	12/1/2014	0
A125	12/1/2012	250	12/1/2012	1
A125	3/1/2013	25	12/1/2012	0
A125	10/1/2013	350	12/1/2012	1
A125	12/1/2013	300	12/1/2013	1
A125	1/1/2014	450	12/1/2013	0
A125	12/1/2014	650	12/1/2014	1
A125	12/1/2014	30	12/1/2014	0
A125	12/1/2014	50	12/1/2014	0
A125	2/1/2015	300	12/1/2014	0
A125	3/1/2015	650	12/1/2014	0

THANK YOU.

Re: I want to get a count for ID number that is 7 months apart from the Start date.

RW9 — Wed, 23 Mar 2016 11:11:58 GMT

Hi,

Sorry, forgot to get back to this. Something like this should work:

data sample;
  input id$4. date payment ;
  attrib id format =$4. informat=$4.;
  attrib date format =date9. informat=date9.;
  attrib payment format =9.0 informat=9.0;
datalines;
A123 01JAN2012 250
A123 01MAR2012 25
A123 01OCT2012 350
A123 01DEC2012 300
A123 01JAN2013 450
A123 01DEC2013 650
A123 01DEC2013 30
A123 01DEC2013 50
A123 01FEB2014 300
A123 01MAR2015 650
A124 01DEC2012 250
A124 01MAR2013 25
A124 01OCT2013 350
A124 01DEC2013 300
A124 01JAN2014 450
A124 01DEC2014 650
A124 01DEC2014 30
A124 01DEC2014 50
A124 01FEB2015 300
A124 01MAR2015 650
A125 01DEC2012 250
A125 01MAR2013 25
A125 01OCT2013 350
A125 01DEC2013 300
A125 01JAN2014 450
A125 01DEC2014 650
A125 01DEC2014 30
A125 01DEC2014 50
A125 01FEB2015 300
A125 01MAR2015 650
;
run;

proc sort data=sample; 
  by id date; 
run;

data want;
  set sample;
  retain newdate count;
  if _n_=1 then do;
    newdate=date;
    count=1;
  end;
  else if intck('month',newdate,date) > 7 then do;
    count=1;
    newdate=date;
  end;
  else count=0;
  format newdate date9.;
run;

Re: I want to get a count for ID number that is 7 months apart from the Start date.

vtan9999 — Thu, 24 Mar 2016 13:35:41 GMT

Thank you ,RW9 (Super User), for your help. With a small change in the program, I get the expected result. Below is revised the program.
data want;
set sample;
by id;
retain newdate count;
if _n_=1 then do;
newdate=date;
count=1;
end;
else if first.id or intck('month',newdate,date) > 7 then do;
count=1;
newdate=date;
end;
else count=0;
format newdate date9.;
run;

proc print data=want; run;