🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

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

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;``````
5 REPLIES 5
Tourmaline | Level 20

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

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

``` ```

Tourmaline | Level 20

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

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;``````
Fluorite | Level 6

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

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.

Tourmaline | Level 20

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

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;
``````
PROC Star

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

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?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Discussion stats
• 5 replies
• 421 views
• 0 likes
• 3 in conversation