topic Re: Collapsing observations based on allowable gaps in SAS Data Management

Collapsing observations based on allowable gaps

Mahip — Mon, 16 Apr 2018 21:48:20 GMT

I have a dataset (created after bunch of data management steps) which is arranged as below. The gap indicator shows the number of gap days ( 1 indicates gap). Now, I would want to allow a gap of 30 days (i.e., I would count them as having no gap if the gap is less than or equal to 30 days). I have tried a few things (used retain, lag statements), but haven't been able to do so.

ID	Gap_indicator	Days
A	0	90
A	1	20
A	0	60
A	1	10
A	0	60
A	1	40
A	0	90

This is how the dataset is laid out. My expected output is (I want to add 30 days of gap to the previous no gap days)

ID	Gap_indicator	Days
A	0	270
A	1	10
A	0	90

I am not able to figure out how to do this. Your help would be much appreciated. Thank you!

Re: Collapsing observations based on allowable gaps

Reeza — Mon, 16 Apr 2018 21:58:55 GMT

Post what you've tried so far please.

Re: Collapsing observations based on allowable gaps

Mahip — Mon, 16 Apr 2018 22:30:23 GMT

Based on some of the readings, I did the following:

data test;
set problem;
by id;
lg_days=lag(days);
if first.id then lg_days=.;
run;

data test1;
merge test test (firstobs=2 rename=(id =id2 days=new_days gap=gap2));
if id ne id2 then new_days=.;
drop id2 gap2;
run;

I created a dataset by bringing the next observation in the same line. I am, however, struggling to collapse the observations with respect to 30 days allowable gap.

Re: Collapsing observations based on allowable gaps

PGStats — Tue, 17 Apr 2018 03:45:10 GMT

Try this:

data have;
input ID $ Gap Days; 
datalines;
A 0 90 
A 1 20 
A 0 60 
A 1 10 
A 0 60 
A 1 40 
A 0 90 
;

data want;
gDays = 0;
do until(last.id);
    set have; by id;
    if gap then do;
        if days > 30 then do;
            gDays = gDays + 30;
            gGap = 0;
            output;
            gDays = days - 30;
            gGap = 1;
            output;
            gDays = 0;
            end;
        else gDays = gDays + days;
        end;
    else gDays = gDays + days;
    end;
if gDays > 0 then do;
    gGap = 0;
    output;
    end;
drop gap days;
rename gGap=Gap gDays=Days;
run;

Re: Collapsing observations based on allowable gaps

Mahip — Tue, 17 Apr 2018 21:54:49 GMT

This works! I will try to parse the codes. One thing I haven't understood so far is, what is the difference between initializing a variable before and after the set statement? Thank you once again.

Re: Collapsing observations based on allowable gaps

PGStats — Tue, 17 Apr 2018 22:10:30 GMT

Imported dataset variables (with a set statement) are overwritten when the set statement executes. Other variables (such as gDays above) are reset to missing at the beginning of the implicit dataset loop (unless they are RETAINed).