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;
... View more