Hi everyone,
My name is Kris, novice SAS programming student. I am dealing with a single string of value that I call Variable 1 here. There is some missing value and I can use the immediate adjacent value to compute it. Problem is there are some really long continuous missing values, and the codes below only deal with up to 3 continuous missing observations.
While I realize I can treat the new Var2 as Var1 and repeat the process, it eventually makes the codes super messy. I wonder if there could be any more efficient way to deal with this, or repeat the codes until there is no more missing observations.
I attached the data here for your reference. I'd appreciate any help or suggestions. Thank you very much.
/*(1) This part creates the Next and Last values of Variable 1 (Var1, NVar1, LVar1)*/
data Want;
set Have;
by CompanyID;
set Have( firstobs = 2 keep = Var1 rename = (Var1 = NVar1 ))
Have( obs = 1 drop = _all_ );
LVar1 = ifn( first.Fundkey, (.), LAG(Var1 ) );
NVar1 = ifn( last.Fundkey, (.), NVar1 );
run;
/*(2) This part define the new variable Var2 if Var1 is missing, based on LVar1 and NVar1*/
data Want_2;
set Want;
by CompanyID;
if Var1 = '.' then do;
if NVar1 ~= '.' then do;
if LVar1 ~= '.' then Var2 = (NVar1 + LVar1)/2;
else Var2 = NVar1;
end;
else Var2 = LVar1;
end;
if Var1~= '.' then Var2 = Var1;
run;
This program won't create Var2, but it will fill in the missing values for Var1. See if it does what you are looking for:
data want;
update have (obs=0) have;
by CompanyID;
output;
run;
If that's not the right result, it would help if you were to post the result you are trying to achieve. The complexity of the code is masking the objective here.
This program won't create Var2, but it will fill in the missing values for Var1. See if it does what you are looking for:
data want;
update have (obs=0) have;
by CompanyID;
output;
run;
If that's not the right result, it would help if you were to post the result you are trying to achieve. The complexity of the code is masking the objective here.
If you have access to proc expand (part of SAS/ETS) then you can interpolate and extrapolate your time series with
proc expand data=have out=want from=month extrapolate;
by CompanyId;
id date;
convert var1;
run;
A data step solution providing linear interpolation (proc expand will do a spline interpolation by default), but no extrapolation, could be:
data want;
do until(last.id);
set have; by id;
if missing(lDate) and missing(var1) then do;
iDate = date;
iVar1 = var1;
output;
end;
else if not missing(var1) then do;
if not missing(lDate) then do;
iDate = intnx("month", lDate, 1);
do while (iDate < date);
iVar1 = (lVar1 * (date - iDate) + var1 * (iDate - lDate)) / (date - lDate);
output;
iDate = intnx("month", iDate, 1);
end;
end;
iDate = date;
iVar1 = var1;
output;
lDate = date;
lVar1 = var1;
end;
end;
drop date var1 lDate lVar1;
rename iVar1=var1 iDate=date;
format iDate yymmdd10.;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.