Identify whether observation was present in different period

[as_methodology]

Hello,

I have a dataset that includes an ID and year that looks somewhat like this:

data have;
input year id;
datalines;
2000 1
2000 2
2000 3
2000 4
2000 5
2001 1
2001 2
2001 3
2001 6
2001 7
2001 8
2002 1
2002 2
2002 6
2002 7
2002 9
2002 10
;
run;

Now, I want a way to check whether an obervation was present in the previous period returning "1", if it did, and "0" otherwise. So for example in year 2002, it shoud return the value "1" for ID "1", "2", "6" and "7" and "0" for the ID "9" and "10".

I have been tinkering around with it, but have not yet reached a conclusion.

Thank you very much for your help.

[PeterClemmensen]

@as_methodology should we only consider the previous period? If we consider ID=1 in 2002, should we only look for ID=1 in 2001 or all preceding periods (years)?

[as_methodology]

Hi draycut

Thanks for the reply. I will only need to check whether it was present in the previous period.

[PeterClemmensen]

The hash object is your friend

data have;
input year id;
datalines;
2000 1
2000 2
2000 3
2000 4
2000 5
2001 1
2001 2
2001 3
2001 6
2001 7
2001 8
2002 1
2002 2
2002 6
2002 7
2002 9
2002 10
;
run;

data want;
    if _N_=1 then do;
        declare hash h (dataset : "have");
        h.definekey ("year", "id");
        h.definedone();
    end;

    set have;

    present = ifn(h.check (key : year-1, key : id), 0, 1);
run;

Result:

year  id  present 
2000  1   0 
2000  2   0 
2000  3   0 
2000  4   0 
2000  5   0 
2001  1   1 
2001  2   1 
2001  3   1 
2001  6   0 
2001  7   0 
2001  8   0 
2002  1   1 
2002  2   1 
2002  6   1 
2002  7   1 
2002  9   0 
2002  10  0 

[Amir]

The hash object is your friend

data have;
input year id;
datalines;
2000 1
2000 2
2000 3
2000 4
2000 5
2001 1
2001 2
2001 3
2001 6
2001 7
2001 8
2002 1
2002 2
2002 6
2002 7
2002 9
2002 10
;
run;

data want;
    if _N_=1 then do;
        declare hash h (dataset : "have");
        h.definekey ("year", "id");
        h.definedone();
    end;

    set have;

    present = ifn(h.check (key : year-1, key : id), 0, 1);
run;

Result:

year  id  present 
2000  1   0 
2000  2   0 
2000  3   0 
2000  4   0 
2000  5   0 
2001  1   1 
2001  2   1 
2001  3   1 
2001  6   0 
2001  7   0 
2001  8   0 
2002  1   1 
2002  2   1 
2002  6   1 
2002  7   1 
2002  9   0 
2002  10  0 

Hi @PeterClemmensen , I liked the solution you posted, but when I try running your two data steps, my "present" column is always set to 1. It looks to me like it should work, as you have shown with your output.

For me, moving the check method to a previous line and testing the result in the ifn() function produces the same result as your solution, i.e.:

data want;
    if _N_=1 then do;
        declare hash h (dataset : "have");
        h.definekey ("year", "id");
        h.definedone();
    end;

    set have;

    rc      = h.check (key : year-1, key : id);
    present = ifn(rc, 0, 1);
run;

The logs are clean when running either version of the code, so the only thing I can think of is SAS version; I'm running on 9.1.3, which version are you using?

Thanks & kind regards,

Amir.

[PeterClemmensen · Accepted solution]

@Amir thank you. 

Just to be a little self-critical looking at my own code, I like this better

data want;
    if _N_=1 then do;
        declare hash h (dataset : "have");
        h.definekey ("year", "id");
        h.definedone();
    end;

    set have;

    present = ifn(h.check (key : year-1, key : id) = 0, 1, 0);
run;

I think it makes it clearer that a successful search should return a 1 to present and a non-succesful search should return zero.

As to your question, I am not sure. I run SAS 9.4Mx, but I do not have SAS available right now. I think you are right though, that this is a version thing. Quite a few differences exist between SAS 9.1 and later versions in the hash field.

@hashman can probably guide you in the right direction 🙂 

[Amir]

@PeterClemmensen, thanks for the response. I was also thinking of an alternative for that same line of code you amended, which is how I discovered the anomaly.

I was going to try / suggest the following, but kept on getting "1" for "present":

    present = h.check (key : year-1, key : id) eq 0;

Kind regards,

Amir. 

[as_methodology]

@PeterClemmensen Works perfectly. Thank you very much!

[PeterClemmensen]

Anytime 🙂

[Satish_Parida]

Please let us know if this worked for you.

data have;
input year id;
datalines;
2000 1
2000 2
2000 3
2000 4
2000 5
2001 1
2001 2
2001 3
2001 6
2001 7
2001 8
2002 1
2002 2
2002 6
2002 7
2002 9
2002 10
;
run;

proc sort data=have;
by id year;
run;

data want;
set have;
by id year;
flag=1;
if first.id = last.id and first.id and first.id then flag=0;
/*else if first.id then flag=0;*/		/*un-comment if necessary*/
run;