I have a list of zip codes associated with IDs and years, and the zip codes for a given ID can be different for each year:
ID zip year
1 2 2018
1 3 2017
1 2 2018
1 3 2017
I need to make it so that the zip code for a given ID is set to the zip code appearing in the most recent year:
ID zip year
1 2 2018
1 2 2017
1 2 2018
1 2 2017
What I have done is try sort by ID and year, but I am lost as to how to move forward with the rest of the logic. I suspect I need to use the first. function, but beyond that I am having no luck.
And a Datastep solution
data have;
input ID zip year ;
cards;
1 2 2018
1 3 2017
1 2 2018
1 3 2017
;
data want;
do _n_=1 by 1 until(last.id);
set have;
by id;
_m=_m max year;
if _m=year then _z=zip;
end;
do _n_=1 to _n_;
set have;
zip=_z;
output;
end;
drop _:;
run;
Hi @aljones1816 Welcome to SAS communities, here is a proc sql welcome for you
data have;
input ID zip year ;
cards;
1 2 2018
1 3 2017
1 2 2018
1 3 2017
;
proc sql;
create table want as
select ID, max(t) as zip,year
from (select ID, zip,year, (year=max(year))*zip as t from have group by id)
group by id;
quit;
And a Datastep solution
data have;
input ID zip year ;
cards;
1 2 2018
1 3 2017
1 2 2018
1 3 2017
;
data want;
do _n_=1 by 1 until(last.id);
set have;
by id;
_m=_m max year;
if _m=year then _z=zip;
end;
do _n_=1 to _n_;
set have;
zip=_z;
output;
end;
drop _:;
run;
Thanks so much, @novinosrin ! I tried all of your solutions and each worked beautifully. I chose this as the answer only because it was the option I ultimately stuck with in my code.
Perhaps your thought process was after this?
proc sort data=have out=_have(rename=(zip=z) keep=id zip);
by id descending year;
run;
data want;
merge have(drop=zip) _have;
by id;
retain zip;
if first.id then zip=z;
drop z;
run;
I see you have multiple observations for the highest year within an ID. In your sample ID 1 have 2 records for 2018. They both have zip=2. Can you assume that the zip code will be constant across all highest year records within an ID? If not, then what rule would you use to choose a zip value?
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.