BookmarkSubscribeRSS Feed
greg6363
Obsidian | Level 7

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.

2 REPLIES 2
Astounding
PROC Star

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
Lapis Lazuli | Level 10

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2273 views
  • 0 likes
  • 3 in conversation