topic dynamically replace values every month in SAS Procedures

dynamically replace values every month

Newbee_MVK — Thu, 16 Feb 2012 09:47:14 GMT

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

dynamically replace values every month

Haikuo — Thu, 16 Feb 2012 18:41:46 GMT

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

dynamically replace values every month

Ksharp — Fri, 17 Feb 2012 06:25:41 GMT

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

dynamically replace values every month

Newbee_MVK — Fri, 17 Feb 2012 10:16:13 GMT

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;

Outpu dataset should look like:

dynamically replace values every month

Newbee_MVK — Fri, 17 Feb 2012 10:16:54 GMT

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;

Outpu dataset should look like:

dynamically replace values every month

Haikuo — Fri, 17 Feb 2012 12:36:54 GMT

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

Re: dynamically replace values every month

Haikuo — Fri, 17 Feb 2012 18:05:01 GMT

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;

if b then do;

do j=1 to dim(_a);

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

end;

run;

HTH,
Haikuo

Re: dynamically replace values every month

Ksharp — Mon, 20 Feb 2012 02:56:47 GMT

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