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.
@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 🙂
@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)?
Hi draycut
Thanks for the reply. I will only need to check whether it was present in the previous period.
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
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.
@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 🙂
@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.
Anytime 🙂
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.