topic Re: create serial number by CustID with related to IND column in SAS Programming

create serial number by CustID with related to IND column

Ronein — Tue, 07 Jan 2025 11:28:52 GMT

Hello

for each customer there are until 12 rows (each row represent account balance in end of month).

There is a column called Ind that get value 1 or 0 (1 means that there is a change in contract number in this month).

I want to create a new varaible caled serial that create a serial numbers for each customerID by the following rule:

If there is a change (means that Ind=1) then go up by one in the serial number until there is another value 1 in field Ind.

what is the way to do it please?

I also show the want data set


data have;
input CustID mon Ind;
cards;
111 1 0
111 2 0
111 3 1
111 4 0
111 5 0
111 6 0
111 7 0
111 8 0
111 9 1
111 10 0
111 11 0
111 12 0
222 1 0
222 2 0
222 3 0
222 4 0
222 5 0
222 6 0
222 7 0
222 8 0
222 9 0
222 10 0
222 11 0
222 12 0
333 1 0
333 2 0
333 3 0
;
Run;

data want;
input CustID mon Ind serial;
cards;
111 1 0 1
111 2 0 1
111 3 1 2
111 4 0 2
111 5 0 2
111 6 0 2
111 7 0 2
111 8 0 2
111 9 1 3
111 10 0 3
111 11 0 3
111 12 0 3
222 1 0 1
222 2 0 1
222 3 0 1
222 4 0 1
222 5 0 1
222 6 0 1
222 7 0 1
222 8 0 1
222 9 0 1
222 10 0 1
222 11 0 1
222 12 0 1
333 1 0 1
333 2 1 2
333 3 0 2
;
Run;

Re: create serial number by CustID with related to IND column

PeterClemmensen — Tue, 07 Jan 2025 11:33:54 GMT

Try this

data want;
   set have;
   by CustID;
   if first.CustID then serial = 1;
   if Ind then serial + 1;
run;

Re: create serial number by CustID with related to IND column

Ksharp — Wed, 08 Jan 2025 02:27:05 GMT

What you gotta do if you have serial number 1s one by one , like this :


data have;
input CustID mon Ind;
cards;
111 1 0
111 2 0
111 3 1
111 4 0
111 5 0
111 6 0
111 7 0
111 8 0
111 9 1
111 10 0
111 11 0
111 12 0
222 1 0
222 2 0
222 3 0
222 4 0
222 5 0
222 6 0
222 7 0
222 8 0
222 9 0
222 10 0
222 11 0
222 12 0
333 1 0
333 2 1
333 3 0
;
Run;
data want;
 set have;
 by CustID;
 if first.CustID then serial=1;
 if not first.CustID and Ind then serial+1;
run;

Re: create serial number by CustID with related to IND column

Ronein — Wed, 08 Jan 2025 07:51:55 GMT

No good,

Look at customer 4 that I added now. He should start from serial 1 and not 2


data have;
input CustID mon Ind;
cards;
111 1 0
111 2 0
111 3 1
111 4 0
111 5 0
111 6 0
111 7 0
111 8 0
111 9 1
111 10 0
111 11 0
111 12 0
222 1 0
222 2 0
222 3 0
222 4 0
222 5 0
222 6 0
222 7 0
222 8 0
222 9 0
222 10 0
222 11 0
222 12 0
333 1 0
333 2 0
333 3 0
444 1 1 
444 2 0 
444 3 0 
444 4 0 
444 5 0 
444 6 0 
444 7 0 
444 8 0 
444 9 1 
444 10 0 
444 11 0 
444 12 1 
;
Run;
data want;
input CustID mon Ind serial;
cards;
111 1 0 1
111 2 0 1
111 3 1 2
111 4 0 2
111 5 0 2
111 6 0 2
111 7 0 2
111 8 0 2
111 9 1 3
111 10 0 3
111 11 0 3
111 12 0 3
222 1 0 1
222 2 0 1
222 3 0 1
222 4 0 1
222 5 0 1
222 6 0 1
222 7 0 1
222 8 0 1
222 9 0 1
222 10 0 1
222 11 0 1
222 12 0 1
333 1 0 1
333 2 1 2
333 3 0 2
444 1 1 1 
444 2 0 1 
444 3 0 1  
444 4 0 1
444 5 0 1 
444 6 0 1 
444 7 0 1 
444 8 0 1 
444 9 1 2 
444 10 0 2  
444 11 0 2 
444 12 1 3 
;
Run;

Re: create serial number by CustID with related to IND column

Ronein — Wed, 08 Jan 2025 07:54:23 GMT

Please look at customer 5


data have;
input CustID mon Ind;
cards;
111 1 0
111 2 0
111 3 1
111 4 0
111 5 0
111 6 0
111 7 0
111 8 0
111 9 1
111 10 0
111 11 0
111 12 0
222 1 0
222 2 0
222 3 0
222 4 0
222 5 0
222 6 0
222 7 0
222 8 0
222 9 0
222 10 0
222 11 0
222 12 0
333 1 0
333 2 0
333 3 0
444 1 1 
444 2 0 
444 3 0 
444 4 0 
444 5 0 
444 6 0 
444 7 0 
444 8 0 
444 9 1 
444 10 0 
444 11 0 
444 12 1 
555 1 0  
555 2 1  
555 3 1   
555 4 1 
555 5 1  
555 6 1  
555 7 1  
555 8 1  
555 9 1 
555 10 1   
555 11 1  
555 12 0 
;
Run;
data want;
input CustID mon Ind serial;
cards;
111 1 0 1
111 2 0 1
111 3 1 2
111 4 0 2
111 5 0 2
111 6 0 2
111 7 0 2
111 8 0 2
111 9 1 3
111 10 0 3
111 11 0 3
111 12 0 3
222 1 0 1
222 2 0 1
222 3 0 1
222 4 0 1
222 5 0 1
222 6 0 1
222 7 0 1
222 8 0 1
222 9 0 1
222 10 0 1
222 11 0 1
222 12 0 1
333 1 0 1
333 2 1 2
333 3 0 2
444 1 1 1 
444 2 0 1 
444 3 0 1  
444 4 0 1
444 5 0 1 
444 6 0 1 
444 7 0 1 
444 8 0 1 
444 9 1 2 
444 10 0 2  
444 11 0 2 
444 12 1 3 
555 1 0 1  
555 2 1 2 
555 3 1 3   
555 4 1 4 
555 5 1 5   
555 6 1 6 
555 7 1 7  
555 8 1 8  
555 9 1 9 
555 10 1 10   
555 11 1 11 
555 12 0 11
;
Run;