topic Re: Setting the values of a column for all members of a by variable group to the value of first in SAS Programming

Setting the values of a column for all members of a by variable group to the value of first

aljones1816 — Wed, 30 Oct 2019 23:16:18 GMT

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.

Re: Setting the values of a column for all members of a by variable group to the value of first

novinosrin — Thu, 31 Oct 2019 01:36:19 GMT

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;

Re: Setting the values of a column for all members of a by variable group to the value of first

novinosrin — Wed, 30 Oct 2019 23:44:17 GMT

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;

Re: Setting the values of a column for all members of a by variable group to the value of first

novinosrin — Thu, 31 Oct 2019 00:01:15 GMT

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;

Re: Setting the values of a column for all members of a by variable group to the value of first

mkeintz — Fri, 01 Nov 2019 00:38:36 GMT

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?

Re: Setting the values of a column for all members of a by variable group to the value of first

aljones1816 — Fri, 08 Nov 2019 20:36:29 GMT

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.