topic Re: generatae new variable by group in SAS Programming

generatae new variable by group

cphd — Mon, 20 Jan 2020 01:43:59 GMT

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

Re: generatae new variable by group

novinosrin — Mon, 20 Jan 2020 02:56:24 GMT

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

Re: generatae new variable by group

Astounding — Mon, 20 Jan 2020 01:53:48 GMT

Logically, this statement does the wrong thing:

   if first.year then maxyear = year;

Instead, use:

   if first.zipcode then maxyear = year;

Re: generatae new variable by group

unison — Mon, 20 Jan 2020 02:46:34 GMT

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