Sas Do Loop + Lag Function?

This is my first post, so please let me know if I'm not clear enough. Here's what I'm trying to do - this is my dataset. My approach for this is a do loop with a lag but the result

Solution 1:

I think this is correct if the asker was mistaken about replacement = Y for obs = 12.

/*Get number of obs so we can build a temporary array to hold the dataset*/
data _null_;
    set have nobs= nobs;
    call symput("nobs",nobs);
    stop;
run;

data want;
    /*Load the dataset into a temporary array*/
    array dates[2,&NOBS] _temporary_;
    if _n_ = 1thendo _n_ = 1by1until(eof);
        set have end = eof;
        dates[1,_n_] = maxdate;
        dates[2,_n_] = 0;
    end;

    set have;

    length replacement $1;

    replacement = 'N';do i = 1to _n_ - 1until(replacement = 'Y');if dates[2,i] = 0and0 <= mindate - dates[1,i] <= 30thendo;
            replacement = 'Y';
            dates[2,i] = _n_;
            replaces = i;
        end;
    end;
    drop i; 
run;

You could use a hash object + hash iterator instead of a temporary array if you preferred. I've also included an extra var, replaces, to show which previous row each row replaces.

Solution 2:

Here is a solution using SQL and hash tables. It is not optimal but it was the first method that sprang to mind.

/* Join the input with its self */
proc sql;
    create table b asselect 
        a1.obs, 
        a2.obs as obs2
    from a as a1
    inner join a as a2
        /* Set the replacement criteria */on a1.maxdate < a2.mindate <= a1.maxdate + 30
    order by a2.obs, a1.obs;
quit;
/* Create a mapping for replacements */
data c;
    set b;
    /* Create two empty hash tables so we can look up the used observations */if _N_ = 1 then do;
        declare hash h();
        h.definekey("obs");
        h.definedone(); 
        declare hash h2();
        h2.definekey("obs2");
        h2.definedone();
    end;
    /* Check if we've already used this observation as a replacement */if h2.find() then do;
        /* Check if we've already replaced his observation  */if h.find() then do;
            /* Add the observations to the hash table and output */
            h2.add();
            h.add();
            output;
        end;
    end;
run;
/* Combine the replacement map with the original data */
proc sql;
    select 
        a.*, 
        ifc(c.obs, "Y", "N") as Replace, 
        c.obs as Replaces
    from a
    left join c
        on a.obs = c.obs2
    order by a.obs;
quit;

There are several ways in which this can be simplified:

• The dates can be brought through the first proc sql
• The if statements can be combined
• The final join could be replaced by a little extra logic in the data step