proc sort data=ret.ra;
by Name zipcode descending year;
run;
data ret.rb;
set ret.ra;
by Name zipcode;
retain maxyear;
if first.year then maxyear = year;
run;
I ran the code above to create maxyear variable by group (Name and Zipcode)
However, the result just shows the same year.. Could you please help how to do it? Thank you.
Name Zipcode year maxyear (what I want)
ABC 30010 2006 2007
ABC 30010 2007 2007
ABC 30011 2005 2005
BCC 30010 2001 2003
BCC 30010 2002 2003
BCC 30010 2003 2003
Logically, this statement does the wrong thing:
if first.year then maxyear = year;
Instead, use:
if first.zipcode then maxyear = year;
Switch to Proc SQL and enjoy autoremerge
proc sql;
create table want as
select *,max(year) as max_year
from have
group by name,zipcode;
quit;
The advantage is sorting isn't required
Logically, this statement does the wrong thing:
if first.year then maxyear = year;
Instead, use:
if first.zipcode then maxyear = year;
@cphd, as @Astounding pointed out, just change first.year to first.zipcode
data have;
input name :$3. zipcode year;
datalines;
ABC 30010 2006
ABC 30010 2007
ABC 30011 2005
BCC 30010 2001
BCC 30010 2002
BCC 30010 2003
;
run;
data desired;
input name :$3. zipcode year maxyear;
datalines;
ABC 30010 2006 2007
ABC 30010 2007 2007
ABC 30011 2005 2005
BCC 30010 2001 2003
BCC 30010 2002 2003
BCC 30010 2003 2003
;
run;
proc sort data=have;
by name zipcode descending year;
run;
data want;
set have;
by name zipcode;
if first.zipcode then
maxyear=year;
retain maxyear;
run;
*Checking that desired=want;
proc sort data=desired;
by name zipcode descending year;
run;
proc compare base=desired compare=want;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: