BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KrisDeng
Obsidian | Level 7

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
Reeza
Super User
Are you replacing missing with the previous value or some other logic?
KrisDeng
Obsidian | Level 7
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.
Astounding
PROC Star

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.

KrisDeng
Obsidian | Level 7
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.
PGStats
Opal | Level 21

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
KrisDeng
Obsidian | Level 7
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.
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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