DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

I want to get a count for ID that is 7 apart from the initial date. 
      
IDDatePayment   
A123Jan-12250   
A123Mar-1225   
A123Oct-12350   
A123Dec-12300   
A123Jan-13450   
A123Dec-13650   
A123Feb-14300   
A123Mar-14300   
A123Apr-14300   
A123May-14300   
A123Jun-14300   
A123Jul-14300   
A123Aug-14300   
A123Sep-14300   
A124Mar-12300   
A124Jun-1225   
A124Sep-1230   
A124Dec-1263   
A124Mar-1315   
A124Jun-1330   
A124Sep-1321   
      
Here is what I want    
      
IDInitial DatePaymentNew DateMonth CntCnt
A123Jan-12250Jan-12 1
A123Mar-1225Jan-12                 -  0
A123Oct-12350Oct-12                   91
A123Dec-12300Oct-12                 -  0
A123May-13450May-13                   71
A123Dec-13650Dec-13                   71
A123Feb-14300Dec-13                 -  0
A123Mar-14300Dec-13                 -  0
A123Apr-14300Dec-13                 -  0
A123May-14300Dec-13                 -  0
A123Jun-14300Dec-13                 -  0
A123Jul-14300Jul-14                   71
A123Aug-14300Jul-14                 -  0
A123Sep-14300Jul-14                 -  0
A124Mar-12300Mar-12 1
A124Jun-1225Mar-12                 -  0
A124Sep-1230Mar-12                 -  0
A124Dec-1263Dec-12                   91
A124Mar-1315Dec-12                 -  0
A124Jul-1330Jul-13                   71
A124Sep-1321Jul-13                 -  0
      
      
SummaryTotal PaymentTotal Count   
A123          4,425                     5   
A1244843   

Accepted Solutions
Solution
‎03-24-2016 10:02 AM
New Contributor
Posts: 3

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

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

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

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

New Contributor
Posts: 3

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

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

idDATEPAYMENTNewDateCnt
A1231/1/20122501/1/20121
A1233/1/2012251/1/20120
A12310/1/201235010/1/20121
A12312/1/201230010/1/20120
A1231/1/201345010/1/20120
A12312/1/201365012/1/20131
A12312/1/20133012/1/20130
A12312/1/20135012/1/20130
A1232/1/201430012/1/20130
A1233/1/20156503/1/20151
A12412/1/201225012/1/20121
A1243/1/20132512/1/2012 
A12410/1/201335010/1/20131
A12412/1/201330010/1/20130
A1241/1/201445010/1/20130
A12412/1/201465012/1/20141
A12412/1/20143012/1/20140
A12412/1/20145012/1/20140
A1242/1/201530012/1/20140
A1243/1/201565012/1/20140
A12512/1/201225012/1/20121
A1253/1/20132512/1/20120
A12510/1/201335012/1/20121
A12512/1/201330012/1/20131
A1251/1/201445012/1/20130
A12512/1/201465012/1/20141
A12512/1/20143012/1/20140
A12512/1/20145012/1/20140
A1252/1/201530012/1/20140
A1253/1/201565012/1/20140

 

THANK YOU.

Super User
Super User
Posts: 7,997

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

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;
Solution
‎03-24-2016 10:02 AM
New Contributor
Posts: 3

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

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 275 views
  • 0 likes
  • 2 in conversation