topic Re: Problem with creating a counter variable in SAS Programming

Problem with creating a counter variable

greg6363 — Fri, 15 Sep 2017 03:15:38 GMT

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.

Re: Problem with creating a counter variable

Astounding — Fri, 15 Sep 2017 04:04:11 GMT

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.

Re: Problem with creating a counter variable

ShiroAmada — Fri, 15 Sep 2017 08:30:28 GMT

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.