topic Re: Start Period and End Period Calculation in SAS Programming

Start Period and End Period Calculation

makarand — Mon, 25 Jul 2016 11:29:05 GMT

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,

Re: Start Period and End Period Calculation

error_prone — Mon, 25 Jul 2016 11:38:36 GMT

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

Re: Start Period and End Period Calculation

RW9 — Mon, 25 Jul 2016 11:40:51 GMT

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.

Re: Start Period and End Period Calculation

Kurt_Bremser — Mon, 25 Jul 2016 11:41:44 GMT

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

Re: Start Period and End Period Calculation

makarand — Mon, 25 Jul 2016 12:05:47 GMT

sorry guys,

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

Month	Start_Period	New_Cust	Cust_Left	End_Period
Apr-11	0	131	22	109
May-11	109	70	85	94
Jun-11	94	55	70	79
Jul-11	79	79	63	95
Aug-11	95	154	96	153
Sep-11	153	185	90	248
Oct-11	248	181	103	326
Nov-11	326	161	114	373
Dec-11	373	231	97	507
Jan-12	507	183	86	604

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

Re: Start Period and End Period Calculation

Kurt_Bremser — Mon, 25 Jul 2016 12:03:33 GMT

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?

Re: Start Period and End Period Calculation

makarand — Mon, 25 Jul 2016 12:08:47 GMT

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

Re: Start Period and End Period Calculation

Kurt_Bremser — Mon, 25 Jul 2016 12:21:31 GMT

@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);

Re: Start Period and End Period Calculation

makarand — Wed, 10 Aug 2016 13:27:18 GMT

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

MonName	Vertical	New_cust	cust_Left	start_period	end_period
Apr-11	- Not Available -	20	.	0	20
Apr-11	C	10	.	20	30
Apr-11	M	10	.	30	40
Apr-11	S	10	.	40	50
May-11	A	20	.	50	70

How should i approach this problem any suggestion?

thanks a ton in advance 🙂