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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.