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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.