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
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;
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
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);
....
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
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
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 ;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.