|
On Thu, 31 Jul 2008 12:17:42 -0400, Ya Huang <ya.huang@AMYLIN.COM> wrote:
>This one may not be very efficient, but should get what you want:
>
>data xx;
>input Start :mmddyy. End :mmddyy.;
>format start end date.;
>cards;
>10/1/2006 12/15/2006
>11/11/2006 1/14/2007
>12/1/2006 1/20/2007
>;
>
>data yy;
> set xx;
>id=_n_;
>do dt=start to end;
> mon=put(dt,monyy.);
> output;
>end;
>run;
>
>proc sql;
>create table zz as
>select distinct id,mon,count(mon) as days,
>intnx('month',input('01'||mon,date.),1)-input('01'||mon,date.) as daysofmon,
>(calculated days)/(calculated daysofmon) as prop
>from yy
>group by id, mon
>order by id, mon
>;
>
>proc transpose data=zz out=ww (drop=_name_);
>by id;
>id mon;
>var prop;
>run;
>
>proc print;
>run;
>
> id DEC06 NOV06 OCT06 JAN07
>
> 1 0.48387 1.00000 1 .
> 2 1.00000 0.66667 . 0.45161
> 3 1.00000 . . 0.64516
>
>
It can be done in one loop (plus the transpose). Data:
data xx;
input Start : mmddyy. End : mmddyy.;
format start end date9.;
cards;
10/1/2006 12/15/2006
11/11/2006 1/14/2007
12/1/2006 1/20/2007
2/6/2007 2/12/2007
;
Notice I've added, for testing purposes, one case where beginning and end
are in same month.
Now count the months, loop through them, and find their endpoints and
lengths. From that the ratio can be computed:
data yy(drop = monthcount monthend monthlength);
set xx;
do monthcount = 0 to intck('month',start,end);
monthend = intnx('month',start,monthcount,'end');
monthlength = day(monthend);
Monthstart = monthend - monthlength + 1;
Points = ( min( end , monthend )
- max( start , monthstart )
+ 1
)
/
monthlength;
output;
end;
format monthstart monyy. points 4.2;
run;
Output:
Start End Monthstart Point
01OCT2006 15DEC2006 OCT06 1.00
01OCT2006 15DEC2006 NOV06 1.00
01OCT2006 15DEC2006 DEC06 0.48
11NOV2006 14JAN2007 NOV06 0.67
11NOV2006 14JAN2007 DEC06 1.00
11NOV2006 14JAN2007 JAN07 0.45
01DEC2006 20JAN2007 DEC06 1.00
01DEC2006 20JAN2007 JAN07 0.65
06FEB2007 12FEB2007 FEB07 0.25
Transpose:
proc transpose data=yy out=zz(drop = _name_);
by start end;
id monthstart;
var points;
run;
Output:
Start End OCT06 NOV06 DEC06 JAN07 FEB07
01OCT2006 15DEC2006 1.00 1.00 0.48 . .
11NOV2006 14JAN2007 . 0.67 1.00 0.45 .
01DEC2006 20JAN2007 . . 1.00 0.65 .
06FEB2007 12FEB2007 . . . . 0.25
>On Thu, 31 Jul 2008 08:08:11 -0700, rong.guo@GMAIL.COM wrote:
>
>>Greetings!
>>
>>I?ve been struggling with a date calculation for a few days. Any help
>>or suggestion will be greatly appreciated!
>>
>>Here is what I have: the starting and ending dates of each promotion.
>>What I need is: to check the promotion availability across months, and
>>calculate points for each promotion each month. If the promotion ran
>>through the whole month, that month gets 1 point. If the promotion was
>>only valid for a partial month, the point gets prorated by the number
>>of days of that month. For instance, if the promotion went from
>>10/1/2006 to 12/15/2006 (first example in the table below), then both
>>Oct-06 and Nov-06 get 1 point, but Dec-06 only get .48 point (=15/31).
>>
>>Please see the example table below, I've tried to arrange the layout,
>>hopefully it is not too messy.
>>
>>Start_date End_date Oct_06 Nov_06 Dec_06 Jan_07
>>10/1/2006 12/15/2006 1.00 1.00 0.48
>>11/11/2006 1/14/2007 0.67 0.45
>>12/1/2006 1/20/2007 1 0.65
>>
>>I've been manually calculating it in Excel, then figured it is ENDLESS
>>and cause a lot of coding errors!! Thanks again!
>>
>>Rong
| 
