BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
makarand
Obsidian | Level 7

Hi, 

I want to add Start_Period and End_Period customer to the calculation..

what I have is New_Cust and Cust_Left

any suggestion 

 

calculation is 

Start_Emp = End_Period cust last month

End Period = Start_Period + New_Cust - Cust_Left

 

Thanks in advance, 

M


Attrition Dat.PNG
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Revised code:

data have;
input new_cust cust_left;
cards;
131 22
70 85
55 70
79 63
154 96
185 90
181 103
161 114
231 97
183 86
;
run;

data want;
* for variable order;
format start_period new_cust cust_left end_period;
set have;
retain end_period 0;
start_period = end_period;
end_period = start_period + new_cust - sum(cust_left,0);
run;

proc print noobs;
run;

Result:

start_                cust_     end_
period    new_cust     left    period

    0        131        22       109 
  109         70        85        94 
   94         55        70        79 
   79         79        63        95 
   95        154        96       153 
  153        185        90       248 
  248        181       103       326 
  326        161       114       373 
  373        231        97       507 
  507        183        86       604 

Where do you get EnD_Cust from?

View solution in original post

8 REPLIES 8
error_prone
Barite | Level 11

Posting input data and the expected result as data-step will make is easier to help you.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Sorry, its not clear to me.  Post example data of what you have - in the form of a datastep.  This will help explain what you have to start.  Then provide the logic for step 1, then step 2 etc.

Kurt_Bremser
Super User
data have;
input month $ new_cust cust_left;
cards;
Apr-11 17 .
May-11 21 .
Jun-11 29 .
Jul-11 108 .
Aug-11 52 .
;
run;

data want;
* for variable order;
format month start_period new_cust cust_left end_period;
set have;
retain end_period 0;
start_period = end_period;
end_period = start_period + new_cust - sum(cust_left,0);
run;

proc print noobs;
run;

Result:

          start_                cust_     end_
month     period    new_cust     left    period

Apr-11        0         17        .         17 
May-11       17         21        .         38 
Jun-11       38         29        .         67 
Jul-11       67        108        .        175 
Aug-11      175         52        .        227 
makarand
Obsidian | Level 7

sorry guys, 

hereis the sample and expected output is calculation for Start_Peroid and End_Period as mentioned before..

 

 

MonthStart_PeriodNew_CustCust_LeftEnd_Period
Apr-11013122109
May-11109708594
Jun-1194557079
Jul-1179796395
Aug-119515496153
Sep-1115318590248
Oct-11248181103326
Nov-11326161114373
Dec-1137323197507
Jan-1250718386604

 
  

+ if I want to subtract variable (Cust_Left) from calculation how can I use SUM equivalent ..

Kurt_Bremser
Super User

Revised code:

data have;
input new_cust cust_left;
cards;
131 22
70 85
55 70
79 63
154 96
185 90
181 103
161 114
231 97
183 86
;
run;

data want;
* for variable order;
format start_period new_cust cust_left end_period;
set have;
retain end_period 0;
start_period = end_period;
end_period = start_period + new_cust - sum(cust_left,0);
run;

proc print noobs;
run;

Result:

start_                cust_     end_
period    new_cust     left    period

    0        131        22       109 
  109         70        85        94 
   94         55        70        79 
   79         79        63        95 
   95        154        96       153 
  153        185        90       248 
  248        181       103       326 
  326        161       114       373 
  373        231        97       507 
  507        183        86       604 

Where do you get EnD_Cust from?

makarand
Obsidian | Level 7
sorry, I was asking for Cust_Left mistakenly typed EnD_Cust !!
+ and - will result in missing calculation result hence asking for alternate of subtraction in SUM equivalent
Kurt_Bremser
Super User

@makarand wrote:
sorry, I was asking for Cust_Left mistakenly typed EnD_Cust !!
+ and - will result in missing calculation result hence asking for alternate of subtraction in SUM equivalent

If you want to avoid any complications caused by missing values, do the calculation like this:

end_period = sum(start_period,new_cust,cust_left*-1);
makarand
Obsidian | Level 7

Thanks guys for the reply,

 this solution works when I have only these variables. but If I add a extra varible like vertical

the end_Period calculation is calculating end period for each increment of different vertical in month

But I want same end_Period value for that month (ie 50 for apr-11) irrespective of vertical

 

MonNameVertical New_custcust_Leftstart_periodend_period
Apr-11- Not Available - 20.020
Apr-11C 10.2030
Apr-11M 10.3040
Apr-11S 10.4050
May-11A 20.5070

 

 

How should i approach this problem any suggestion?

thanks a ton in advance 🙂

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, 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
  • 8 replies
  • 1357 views
  • 3 likes
  • 4 in conversation