How Do I Retain Values between a data step using an array

[sds2ga]

Good evening,

I am trying to figure out what I believe should be a relatively straight forward problem im SAS. Please see the attached SAS file which describes what I am trying to do.

Basically, I have a data set that has a bunch of ID's and these ID's are repeated throughout the dataset for the specified time period (5 months in the example attached). So each of those ID's would have 5 records in this data set, for the 5 months we are covering with a value for a new monthly demand.

For the first record of each ID in this dataset, we have the previous 3 months of demand. However, I want to use an array to shift that past demand over and feed in the new value that we have in the "new_demand" column.

Please let me know if my explanation makes sense. If anyone here could help me with this issue it would be greatly appreciated! 

data existing;
   infile datalines delimiter=','; 
   input id $ monthid new_demand month1 month2 month3;
   datalines;                      
1,1,2,3,4,5
1,2,3,.,.,.
1,3,0,.,.,.
1,4,3,.,.,.
1,5,1,.,.,.
2,1,2,3,4,5
2,2,3,.,.,.
2,3,0,.,.,.
2,4,3,.,.,.
2,5,1,.,.,.
;
run;


data new;
	set existing;
	by id;
	retain month1-month3;
	array monthly[3] month1-month3;

	put "month1=" month1;
	put "month2=" month2;

	demand_lag = lag(new_demand);
	monthlag1 = lag(month1);
	monthlag2 = lag(month2);
	monthlag3 = lag(month3);

	array lag_test[3] monthlag1-monthlag3;

		do i=dim(monthly) to 1 by -1;
			if i=1 then do;
				monthly[i]=demand_lag;
			end;
			else do;
				monthly[i]=lag_test[i-1];
			end;
		end;

run;


data desired;
   infile datalines delimiter=','; 
   input id $ monthid new_demand month1 month2 month3;
   datalines;                      
1,1,2,3,4,5
1,2,3,2,3,4
1,3,0,3,2,3
1,4,3,0,3,2
1,5,1,3,0,3
2,1,2,3,4,5
2,2,3,2,3,4
2,3,0,3,2,3
2,4,3,0,3,2
2,5,1,3,0,3
;
run;

[PGStats · Accepted solution]

Use a temporary array

ata desired;
array m_{99} _temporary_;
set existing; by id;
array month{*} month1-month3;
if first.id then do;
    call missing(of m_{*});
    do i = 1 to dim(month);
        m_{i+1} = month{i};
        end;
    end;
else do;
    do i = dim(month) to 1 by -1;
        m_{i+1}  = m_{i};
        end;
    end;
m_{1} = new_demand;
do i = 1 to dim(month);
    month{i} = m_{i+1};
    end;
drop i;
run;

[novinosrin]

Here is another way:

data existing;

   infile datalines delimiter=',';

   input id $ monthid new_demand month1 month2 month3;

   datalines;                     

1,1,2,3,4,5

1,2,3,.,.,.

1,3,0,.,.,.

1,4,3,.,.,.

1,5,1,.,.,.

2,1,2,3,4,5

2,2,3,.,.,.

2,3,0,.,.,.

2,4,3,.,.,.

2,5,1,.,.,.

;

run;

data desired;

length   _month1 _month2 _month3 8;

retain      _month1 _month2 _month3;

if (_n_ = 1) then do;

    declare hash myhash();

    rc = myhash.definekey('id');

    rc = myhash.definedata('_month1','_month2','_month3');

    myhash.definedone();

 end;

 set existing;

 by id;

 if first.id then do;

 myhash.clear();

_month1= new_demand;

_month2=month1 ;

_month3=month2;

myhash.add();

end;

else do;

myhash.find();

month1=    _month1;

month2=    _month2;

month3=    _month3;

_month1= new_demand;

_month2=month1 ;

_month3=month2;

myhash.replace();

end;

drop _: rc;

run;

Regards,

Naveen Srinivasan

[Tom]

Here is a method that works for your data. It might not work if observations other than the first on had values in the MONTHx variables.

Basically convert the first row into 4 observations and the others into 1 and then use LAGn() functions to generate the MONTHn variables.

data want ;
  set have ;
  by id;
  array val month3-month1 new_demand ;
  do i=1 to dim(val) ;
     value = val(i) ;
     if first.id or i=dim(val) then do;
       new1 = lag1(value);
       new2 = lag2(value);
       new3 = lag3(value);
     end;
  end;
  drop i new_demand month1-month3 ;
  rename value=new_demand new1-new3=month1-month3;
run;

[PGStats]

Wow, @Toms' code is a rare example of the lag function being put to good use inside a conditional block.

[sds2ga]

Thanks for all the help all, not sure why I was struggling with this so much. I think the temporary array solution gets at what I need the most.