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;
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;
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;
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
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;
Wow, @Toms' code is a rare example of the lag function being put to good use inside a conditional block.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.