dynamically replace values every month

[Newbee_MVK]

Thanks for all your answers. Certainly they help to me some extent. Theis is a small change which I have incorporated in the below code.

data act;
input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;
cards;
actuals a 1 2 3 4 5 0 0 0 0 0 0 0
actuals b 6 7 8 9 4 0 0 0 0 0 0 0
actuals c 5 4 3 2 1 0 0 0 0 0 0 0
;
run;

data fct;
input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;
cards;
fcast a 9 4 0 8 0 1 2 3 4 5 6 7
fcast b 0 1 2 0 3 7 8 9 1 2 3 4
fcast c 6 0 0 1 0 5 6 7 3 2 1 3
;
run;

For example present working month is May. So i will get Actual data up to May month. The forecast data starts
present month+1(May+1=June).So the forcast data is available from June onwords to till year end. My question is to replace the forecast data
with actual data up to May month if the data is zero or any numeric values.

Outpu dataset should look like:

title   prod jan feb mar apr may jun jul aug sep oct nov dec
actuals  a   1   2   3     4     5   0    0    0    0    0    0   0
actuals  b   6   7   8     9     4   0    0    0    0    0    0   0
actuals  c   5   4   3     2     1   0    0    0    0    0    0   0
fcast     a   1   2   3     4      5   1    2    3    4    5    6   7
fcast     b   6   7   8     9      4   7    8    9    1    2    3   4
fcast     c   5   4   3     2     1    5    6    7    3    2    1   3

[Haikuo]

The following code works for your as-is data, based on an assumption that records are pulled in turn from both incoming tables.

data act;

input title $ jan feb mar apr may jun jul aug sep oct nov dec;

cards;

actuals 1 2 3 4 5 0 0 0 0 0 0 0

actuals 1 2 3 4 5 6 0 0 0 0 0 0

;

run;

data fct;

input title $ jan feb mar apr may jun jul aug sep oct nov dec;

cards;

fcast 0 0 0 0 0 1 2 3 4 5 6 7

fcast 0 0 0 0 0 0 1 2 3 4 5 6

;

run;

data actfct (drop=l: i);

set act;

array v(*) jan--dec;

array lg(*) l1-l12;

do i=1 to dim(v);

   lg(i)=v(i);

end;

output;

set fct;

   do i=1 to dim(v);

       v(i)=ifn(v(i)=0,lg(i),v(i));

end;

output;

run;

proc sort data=actfct;

by title;

run;

proc print;run;

Regards,

Haikuo

[Newbee_MVK]

Thanks for all your answers. Certainly they help to me some extent. Theis is a small change which I have incorporated in the below data.

data act;
input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;
cards;
actuals a 1 2 3 4 5 0 0 0 0 0 0 0
actuals b 6 7 8 9 4 0 0 0 0 0 0 0
actuals c 5 4 3 2 1 0 0 0 0 0 0 0
;
run;

data fct;
input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;
cards;
fcast a 9 4 0 8 0 1 2 3 4 5 6 7
fcast b 0 1 2 0 3 7 8 9 1 2 3 4
fcast c 6 0 0 1 0 5 6 7 3 2 1 3
;
run;

For example present working month is May. So i will get Actual data up to May month. The forecast data starts
present month+1(May+1=June).So the forcast data is available from June onwords to till year end. My question is to replace the forecast data
with actual data up to May month if the data is zero or any numeric values.

Outpu dataset should look like:

title   prod jan feb mar apr may jun jul aug sep oct nov dec
actuals  a   1   2   3     4     5   0    0    0    0    0    0   0
actuals  b   6   7   8     9     4   0    0    0    0    0    0   0
actuals  c   5   4   3     2     1   0    0    0    0    0    0   0
fcast     a   1   2   3     4      5   1    2    3    4    5    6   7
fcast     b   6   7   8     9      4   7    8    9    1    2    3   4
fcast     c   5   4   3     2     1    5    6    7    3    2    1   3

[Haikuo]

The original code has been tweaked a bit to accommodate your new situation:

/*option 1: automatically detect '0' as being no data for act*/

data actfct (drop=l: i);

set act;

array v(*) jan--dec;

array lg(*) l1-l12;

do i=1 to dim(v);

   lg(i)=v(i);

end;

output;

set fct;

   do i=1 to dim(v);

       v(i)=ifn(lg(i)=0,v(i),lg(i));

end;

output;

run;

/*option 2: arbituary setting the current month for update, in this case: April*/

%let i=4;

data actfct (drop=l: i);

set act;

array v(*) jan--dec;

array lg(*) l1-l12;

do i=1 to dim(v);

   lg(i)=v(i);

end;

output;

set fct;

   do i=1 to dim(v);

       v(i)=ifn(i<=&i,lg(i),v(i));

end;

output;

run;

Please note: both tables have to be in the relationship of 1:1.

Regards,

Haikuo

[Ksharp]

You need to post some more data to clear your question more.

For  the data you posted and what I understand.

data act;
input title $ jan feb mar apr may jun jul aug sep oct nov dec;
cards;
actuals 1 2 3 4 5 0 0 0 0 0 0 0
actuals 1 2 3 4 5 6 0 0 0 0 0 0
;
run;

data fct;
input title $ jan feb mar apr may jun jul aug sep oct nov dec;
cards;
fcast 0 0 0 0 0 1 2 3 4 5 6 7
fcast 0 0 0 0 0 0 1 2 3 4 5 6
;
run;

data actfct(drop=_: i j);
set act(in=a) fct(in=b);
retain _jan _feb _mar _apr _may _jun _jul _aug _sep _oct _nov _dec;
array _a{*} jan feb mar apr may jun jul aug sep oct nov dec;
array _b{*} _jan _feb _mar _apr _may _jun _jul _aug _sep _oct _nov _dec;
if a then do;
             do i=1 to dim(_a);
               _b{i}=_a{i};
             end;
          end;
if b then do;
             do j=1 to dim(_a);
              _a{j}=ifn(_a{j}=0,_b{j},_a{j});
             end;
          end;
run;

Ksharp

[Newbee_MVK]

Thanks for all your answers. Certainly they help to me some extent. Theis is a small change which I have incorporated in the below code.

data act;
input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;
cards;
actuals a 1 2 3 4 5 0 0 0 0 0 0 0
actuals b 6 7 8 9 4 0 0 0 0 0 0 0
actuals c 5 4 3 2 1 0 0 0 0 0 0 0
;
run;

data fct;
input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;
cards;
fcast a 9 4 0 8 0 1 2 3 4 5 6 7
fcast b 0 1 2 0 3 7 8 9 1 2 3 4
fcast c 6 0 0 1 0 5 6 7 3 2 1 3
;
run;

For example present working month is May. So i will get Actual data up to May month. The forecast data starts
present month+1(May+1=June).So the forcast data is available from June onwords to till year end. My question is to replace the forecast data
with actual data up to May month if the data is zero or any numeric values.

Outpu dataset should look like:

title   prod jan feb mar apr may jun jul aug sep oct nov dec
actuals  a   1   2   3     4     5   0    0    0    0    0    0   0
actuals  b   6   7   8     9     4   0    0    0    0    0    0   0
actuals  c   5   4   3     2     1   0    0    0    0    0    0   0
fcast     a   1   2   3     4      5   1    2    3    4    5    6   7
fcast     b   6   7   8     9      4   7    8    9    1    2    3   4
fcast     c   5   4   3     2     1    5    6    7    3    2    1   3

[Haikuo]

I think Ksharp's slick code needs some tweak before it can work flawlessly again. Ksharp's code only retains the last record of act instead of one by one, and since in your original post, two records are largely the same in act table, which makes the outcome difficult to analyze. It really took me a while to figure it out. Here, an index variable 'n' has been introduced to each of your incoming tables, Ksharp's code then works like charm:

data act;

input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;

n+1;

cards;

actuals a 1 2 3 4 5 0 0 0 0 0 0 0

actuals b 6 7 8 9 4 0 0 0 0 0 0 0

actuals c 5 4 3 2 1 0 0 0 0 0 0 0

;

run;

data fct;

input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;

n+1;

cards;

fcast a 9 4 0 8 0 1 2 3 4 5 6 7

fcast b 0 1 2 0 3 7 8 9 1 2 3 4

fcast c 6 0 0 1 0 5 6 7 3 2 1 3

;

run;

data actfct(drop=n i j);

set act(in=a) fct(in=b);

by n;

/*retain _jan _feb _mar _apr _may _jun _jul _aug _sep _oct _nov _dec;*/

array _a{*} jan feb mar apr may jun jul aug sep oct nov dec;

array _b{12} _temporary_;

if a then do;

             do i=1 to dim(_a);

               _b{i}=_a{i};

             end;

          end;

if b then do;

             do j=1 to dim(_a);

              _a{j}=ifn(_b{j}=0,_a{j},_b{j});

             end;

          end;

run;

HTH,
Haikuo

[Ksharp]

It looks like you need update statement.

data act;
input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;
n+1;
cards;
actuals a 1 2 3 4 5 0 0 0 0 0 0 0
actuals b 6 7 8 9 4 0 0 0 0 0 0 0
actuals c 5 4 3 2 1 0 0 0 0 0 0 0
;
run;

 

data fct;
input title $ prod $ jan feb mar apr may jun jul aug sep oct nov dec;
n+1;
cards;
fcast a 9 4 0 8 0 1 2 3 4 5 6 7
fcast b 0 1 2 0 3 7 8 9 1 2 3 4
fcast c 6 0 0 1 0 5 6 7 3 2 1 3
;
run;


%let start_month=5 ;
data temp(drop= title prod i );
set act;
array _a{*} jan feb mar apr may jun jul aug sep oct nov dec;
do i=1 to dim(_a);
 if i gt &start_month then call missing(_a{i});
end;
run;
data fct(drop=n);
 update fct temp;
 by n;
run; 
data want;
 set act(drop=n) fct;
run;

Ksharp