How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

 

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;

 

 

CID.jpg


Accepted Solutions
Solution
‎01-11-2018 02:07 PM
Super User
Posts: 6,629

Re: How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

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.

View solution in original post


All Replies
Super User
Posts: 23,262

Re: How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

Are you replacing missing with the previous value or some other logic?
Contributor
Posts: 22

Re: How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

I expected to calculate the missing obs for recent available data, but the amount of missing data is too much. I'm open to and grateful for any suggestions.
Solution
‎01-11-2018 02:07 PM
Super User
Posts: 6,629

Re: How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

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.

Contributor
Posts: 22

Re: How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

Posted in reply to Astounding
I dont quite get the codes, but from the first look it worked out quite well. Thank you for it, though I'd appreciate any further explanation.
Esteemed Advisor
Posts: 5,479

Re: How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

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; 
PG
Contributor
Posts: 22

Re: How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

Thank you for your solution. I tried this code and it gave me some extreme results here and there, while sometimes I feel like the missing data is generated from the previous data, not the following data. I'm kinda looking forward to some balance between both. I'm trying the codes in your second post about linear interpolation. Again I truly appreciate the help and will get back to you later.
Esteemed Advisor
Posts: 5,479

Re: How do I Repeat a chunk of codes until there's no more missing observations. (SAS 9.4)

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;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 201 views
  • 5 likes
  • 4 in conversation