Solved
Contributor
Posts: 25

# 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

M

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

## 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
How to convert datasets to data steps
How to post code

All Replies
Regular Contributor
Posts: 209

## 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
Posts: 9,599

## 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: 10,217

## 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
How to convert datasets to data steps
How to post code
Contributor
Posts: 25

## Re: Start Period and End Period Calculation

[ Edited ]

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

Solution
‎07-25-2016 08:06 AM
Super User
Posts: 10,217

## 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
How to convert datasets to data steps
How to post code
Contributor
Posts: 25

## Re: Start Period and End Period Calculation

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: 10,217

## 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
How to convert datasets to data steps
How to post code
Contributor
Posts: 25

## Re: Start Period and End Period Calculation

[ Edited ]

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

☑ This topic is solved.