base sas

[sunilreddy]

Hi,

I've a numeric column REQ_ORD and need to calculate "NF" column on group variables PROD, CUST, PER_DT.

In below example, need to pass dynamic date value '01-JUL-2013' for PER_DT column. need to consider previous 3 records(01-APR-2013, 01-MAY-2013, 01-JUN-2013) from dynamic date value('01-JUL-2013')

Mentioned New calculated column "NF"  with calculation description(NF description). To calculate NF for 01-Jul-2013, Avg of previous 3 records (01-APR-2013,01-MAY-2013, 01-JUN-2013) of REQ_ORD, To calculate NF for 01-Aug-2013, Avg of first 2 records(01-MAY-2013, 01-JUN-2013) of REQ_ORD and Third record(01-JUL-2013) of NF, To calculate NF for 01-Sep-2013, Avg of first record(01-Jun-2013) of REQ_ORD and last two records (01-JUL-2013, 01-AUG-2013) of NF,

Always i need to start from previous three records of dynamic date value. ex: if date value 01-JUN-2013, it considers (01-MAR-2013, 01-APR-2013, 01-MAY-2013) for NF of 01-JUN-2013

Scenario has included in below and also attached.

PROD	CUST	REQ_ORD_QTY	NF	PER_DT	NF description
130	1214	24		01-Apr-13
130	1214	144		01-May-13
130	1214	57		01-Jun-13
130	1214	123	75	01-Jul-13	REQ_ORD_APR,REQ_ORD_MAY, REQ_ORD_JUN
130	1214	0	92	01-Aug-13	REQ_ORD_MAY,REQ_ORD_JUN, NF_JUL
130	1214	2	74.66666667	01-Sep-13	REQ_ORD_JUN,NF_JUL, NF_AUG
130	1214	3	80.55555556	01-Oct-13	NF_JUL, NF_AUG,NF_SEP
130	1214	4	82.40740741	01-Nov-13	NF_AUG,NF_SEP,NF_OCT
130	1214	5	79.20987654	01-Dec-13	NF_SEP,NF_OCT,NF_NOV
130	1214	6		01-Jan-14	NF_OCT,NF_NOV,NF_DEC
130	1214			Till 01-Apr-15	NF_JAN,NF_FEB,NF_MAR

Message was edited by: Sunil Kumar Included one more example in attached file calc.xls, want to calculate new column E(NF) and Column F is pointing calculation of column E in output tab from input data(tab: input). want to generate number of records in output (tab: output)will be dynamamic passed date(01JUL2013) + 20 months records from 01JUL2013 + previous three records from 01JUL2013

[DBailey]

I don't quite understand your desired logic...but you can join the table to itself with appropriate date criteria to get all of the values on one row...then do the math.

proc sql;

create table want as

select

     t1.prod,

     t1.cust,

     t1.req_ord_qty,

     t2.req_ord_qty as req_ord_qty_2,

     t3.req_ord_qty as req_ord_qtr_3

.

.

.

from

     have t1

     left join have t2

          on t1.prod=t2.prod

               and t1.cust=t2.cust

               and intnx('month',t1.per_dt,-1,'begin')=t2.per_dt

     left join have t3

          on t1.prod=t3.prod

               and t1.cust=t3.cust

               and intnx('month',t1.per_dt,-2,'begin')=t3.per_dt

     left join have t4

          on t1.prod=t4.prod

               and t1.cust=t4.cust

               and intnx('month',t1.per_dt,-3,'begin')=t4.per_dt

     left join have t5

          on t1.prod=t5.prod

               and t1.cust=t5.cust

               and intnx('month',t1.per_dt,-4,'begin')=t5.per_dt

     left join have t6

          on t1.prod=t6.prod

               and t1.cust=t6.cust

               and intnx('month',t1.per_dt,-5,'begin')=t6.per_dt

     left join have t7

          on t1.prod=t7.prod

               and t1.cust=t7.cust

               and intnx('month',t1.per_dt,-6,'begin')=t7.per_dt

;

quit;

[sunilreddy]

Any updaes on below

Included one more example in attached file calc.xls, want to calculate new column E(NF) and Column F is pointing calculation of column E in output tab from input data(tab: input). want to generate number of records in output (tab: output)will be dynamamic passed date(01JUL2013) + 20 months records from 01JUL2013 + previous three records from 01JUL2013

[Patrick]

Not tested and will need a bit of tweaking for cases with missing months ( reset counter). Is this what you are after?

...

by prod cust per_dt;

if first.cust then count=0;

count+1;

nf=sum(lag(req_ord_qty),lag2(..),lag3(...))/3;

if count<3 then call missing(NF);

....

[sunilreddy]

Hi Patrick,

Thanks for your response. I am looking almost similar to what you are

I've also tried, but not getting expected output.

data out1;

input PROD $ CUST $ REQ_ORD FORE DATE9.;

FORMAT FORE DATE9.;

DATALINES4;

130 1214 24 01APR2013
130 1214 144 01MAY2013
130 1214 57 01JUN2013
130 1214 123 01JUL2013
130 1214 0 01AUG2013
130 1214 2 01SEP2013
130 1214 3 01OCT2013
130 1214 3 01NOV2013
130 1214 3 01DEC2013
130 1214 3 01JAN2014

;;;;

RUN;

data OUT2;
set OUT1;
by PROD
   CUST
   FORE;

if first.cust then do;
count=0;
end;

count +1;

lag1=lag1(REQ_ORD);
if count le 1 then lag1=.;

lag2=lag2(REQ_ORD);
if count le 2 then lag2=.;

lag3=lag3(REQ_ORD);
if count le 3 then lag3=.;

NF_QTY=sum(lag1, lag2, lag3)/3;
if count le 3 then NF_QTY=.;

run;

i need output would be  in attached image

[Peter_C]

i seem to remember this from somewhere else.....

A rolling N-row average of the incoming data  should be easy but care is needed to exclude the current value. Here is how i remember it

%let periods = 3 ; * or longer when wanted;

%let forecast_periods = 20 ;

DATA rolling ;

ARRAY rolls(&periods) _temporary_ ;

NFe = MEAN( OF rolls(*) ) ;

* now insert latest data into rolls array until end of data;

SET  original_data end= lastdatarow;

Row+1:

Pointer = MOD( Row, &periods ) +1 ;

rolls( Pointer ) = nf ;

*release data row and finish, unless end of input ;

Output ;

If not lastdatarow then return ;

Do _n_ = rows to ( rows + &forecast_periods ) ;

NFe = MEAN( OF rolls(*) ) ;

Output;

per_dt = intnx( 'month', per_dt, 1 ) ;

Pointer = MOD( _n_, &periods ) +1 ;

Rolls(_n_) = NFe ;

End ;

STOP ;

RUN ;

beware this code is untested

Hope it helps

peterC

[sunilreddy]

Hi Peter,

I've tested your code as below but still not giving expected

data out1;

input PROD $ CUST $ NF PER_DT DATE9.;

FORMAT PER_DT DATE9.;

DATALINES4;

130 1214 24 01APR2013
130 1214 144 01MAY2013
130 1214 57 01JUN2013
130 1214 123 01JUL2013
130 1214 0 01AUG2013
130 1214 2 01SEP2013
130 1214 3 01OCT2013
130 1214 3 01NOV2013
130 1214 3 01DEC2013

;;;;

RUN;

%let periods = 3 ; * or longer when wanted;

%let forecast_periods = 10 ;

DATA rolling ;

ARRAY rolls(&periods) _temporary_ ;

NFe = MEAN( OF rolls(*) ) ;

* now insert latest data into rolls array until end of data;

SET  out1 end= lastdatarow;

Row+1;

Pointer = MOD( Row, &periods ) +1 ;

rolls( Pointer ) = nf ;

*release data row and finish, unless end of input ;

Output ;

If not lastdatarow then return ;

Do _n_ = row to ( row + &forecast_periods ) ;

NFe = MEAN( OF rolls(*) ) ;

Output;

per_dt = intnx( 'month', per_dt, 1 ) ;

Pointer = MOD( _n_, &periods ) +1 ;

Rolls(pointer) = NFe ;

End ;

STOP ;

RUN ;