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 ;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.