topic Re: Identify whether observation was present in different period in SAS Programming

Identify whether observation was present in different period

as_methodology — Mon, 27 Jan 2020 06:42:34 GMT

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.

Re: Identify whether observation was present in different period

PeterClemmensen — Mon, 27 Jan 2020 07:20:32 GMT

@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)?

Re: Identify whether observation was present in different period

as_methodology — Mon, 27 Jan 2020 07:25:09 GMT

Hi draycut

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

Re: Identify whether observation was present in different period

PeterClemmensen — Mon, 27 Jan 2020 08:04:25 GMT

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

Re: Identify whether observation was present in different period

Satish_Parida — Mon, 27 Jan 2020 08:05:54 GMT

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;

Re: Identify whether observation was present in different period

Amir — Mon, 27 Jan 2020 13:57:24 GMT

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.

Re: Identify whether observation was present in different period

PeterClemmensen — Mon, 27 Jan 2020 14:18:26 GMT

@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 🙂

Re: Identify whether observation was present in different period

Amir — Mon, 27 Jan 2020 14:42:31 GMT

@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.

Re: Identify whether observation was present in different period

as_methodology — Thu, 30 Jan 2020 09:32:38 GMT

@PeterClemmensen Works perfectly. Thank you very much!

Re: Identify whether observation was present in different period

PeterClemmensen — Thu, 30 Jan 2020 09:36:42 GMT

Anytime 🙂