Solved
New Contributor
Posts: 2

# Repeat the value in a varaible across the rows.

Hi there

Fairly new to SAS and would really appreciate help with a particular problem.

I have a dataset comprising multiple rows per person.  For each person, I would like to transfer the maximum value from variable 1 (var1) onto all the other rows for that person and into a new variable (newvar).

e.g.

ID   Var1

1    4

1    5

1    2

1    2

--

2    6

2    9

2    1

2    0

....would become.....

ID   Var1   Newvar

1    4         5

1    5         5

1    2         5

1    2         5

--

2    6         9

2    9         9

2    1         9

2    0         9

Thank you

Accepted Solutions
Solution
‎01-29-2016 10:46 AM
Super User
Posts: 8,093

## Re: Repeat the value in a varaible across the rows.

This is easy to do in PROC SQL because SAS will automatically remerge summary statistics for you.

``````proc sql ;
create table want as
select *,max(var1) as newvar
from have
group by id
;
quit;``````

You could do it in a data step using a technique known as DOW loops. The data must be sorted by ID .

``````data want ;
do until (last.id);
set have;
by id;
newvar=max(newvar,var1);
end;
do until (last.id);
set have;
by id;
output;
end;
run;
``````

All Replies
Super User
Posts: 9,599

## Re: Repeat the value in a varaible across the rows.

Hi,

This selects all the data, then left joins the max value per ID group.

```proc sql;
create table WANT as
select  A.*,
B.MAX_VALUE
from    HAVE A
left join (select ID,
max(VAR1) as MAX_VALUE
from HAVE
group by ID) B
on       A.ID=B.ID;
quit;```
Super Contributor
Posts: 490

## Re: Repeat the value in a varaible across the rows.

``````proc sort data=have;
by id descending var1;
run;
data want;
set have;
retain newvar;
by id descending var1;
if first.id then do;
newvar=var1;
end;
run;``````
Solution
‎01-29-2016 10:46 AM
Super User
Posts: 8,093

## Re: Repeat the value in a varaible across the rows.

This is easy to do in PROC SQL because SAS will automatically remerge summary statistics for you.

``````proc sql ;
create table want as
select *,max(var1) as newvar
from have
group by id
;
quit;``````

You could do it in a data step using a technique known as DOW loops. The data must be sorted by ID .

``````data want ;
do until (last.id);
set have;
by id;
newvar=max(newvar,var1);
end;
do until (last.id);
set have;
by id;
output;
end;
run;
``````

New Contributor
Posts: 2

## Re: Repeat the value in a varaible across the rows.

Thank you so much for your solutions. Your experise are much appreciated!

I meant to say that I have hundreds of millions of observations so an efficient command will be the most useful.  I will try these and see....

Thanks again

Posts: 3,852