DATA Step, Macro, Functions and more

Start Period and End Period Calculation

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Start Period and End Period Calculation

[ Edited ]

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

Accepted Solutions
Solution
‎07-25-2016 08:06 AM
Super User
Posts: 7,827

Re: Start Period and End Period Calculation

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Frequent Contributor
Posts: 149

Re: Start Period and End Period Calculation

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

Super User
Super User
Posts: 7,975

Re: Start Period and End Period Calculation

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.

Super User
Posts: 7,827

Re: Start Period and End Period Calculation

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 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 25

Re: Start Period and End Period Calculation

[ Edited ]
Posted in reply to KurtBremser

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 ..

Solution
‎07-25-2016 08:06 AM
Super User
Posts: 7,827

Re: Start Period and End Period Calculation

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 25

Re: Start Period and End Period Calculation

Posted in reply to KurtBremser
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
Super User
Posts: 7,827

Re: Start Period and End Period Calculation


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);
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 25

Re: Start Period and End Period Calculation

[ Edited ]
Posted in reply to KurtBremser

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 :-)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 380 views
  • 3 likes
  • 4 in conversation