Problem with creating a counter variable

[greg6363]

In my data set, I have a Month_Date field and a CustomerNo field.  I want to add a Counter field that starts from zero (0) for each Month_Date associated with a particular CustomerNo.  My macro defined a 36 month time frame but not all CustomerNo will have 36 observations.  Some might have 1 or 2 or 3 all the way up to 36.  This is how I want the output to appear:

MonthDate     CustomerNo     Counter

201410               288                  0

201411               288                  1

201412               288                  2

201501               288                  3

201502               288                  4

201410               351                  0

201411               351                  1

201412               351                  2

In this case, CustomerNo 288 has 5 observations and CustomerNo 351 has 3 observations.  The Counter should start from zero and end with the last given MonthDate for the respective CustomerNo. 

This is how I put together my code in a data step:

data Final;

set Master5;

by CustomerNo;

retain count;

label count='Counter";

keep MonthDate CustomerNo count;

if first.CustomerNo then 

count = 0;

else count = count + 1;

run;

Unfortunately, the output for this code generates a counter (and outputs each MonthDate) for each of the 36 months set in my original macro, even if there is no MonthDate record in the data set for that particular CustomerNo. 

What am I doing wrong in this code?

Any assistance would be greatly appreciated.  Thanks.

[Astounding]

Your DATA step is fine.  Check your MASTER5 data.  It looks like your "macro" (whatever you mean by that) accidentally created a data set with 36 observations for each customer.

[ShiroAmada]

Try this....

data HAVE;
  do i=1 to 12;
   format date date9.;
    date=mdy(i,i,2017);
drop i;
     output;
  end;
run;

data WANT;
  set 
  HAVE (in=a obs=3)
  HAVE (in=b firstobs=2 obs=8)
  HAVE (in=c firstobs=7)
  HAVE (IN=d obs=1);

if b then custno=2;
if c then custno=3;
if d then custno=4;
run;

proc sort;
  by custno date;
run;

data WANT;
  set WANT;
  by custno;
if first.custno then counter=0;
  else counter+1;
run;

 
     

Hope this helps.