09-14-2017 11:15 PM
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:
keep MonthDate CustomerNo count;
if first.CustomerNo then
count = 0;
else count = count + 1;
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.
09-15-2017 12:03 AM - edited 09-15-2017 12:04 AM
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.
09-15-2017 04:29 AM - edited 09-15-2017 04:30 AM
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.