## Filling a cell with the value above by group

Solved
Regular Contributor
Posts: 180

# Filling a cell with the value above by group

Hi everyone

I have a dataset with a column containing missing values.

I am trying to fill these missing values from the cells above provided that the two rows belong to the same group.

Here is the dataset:

``````data a;
input N	Group	Var;
datalines;

1	1	3
2	1	6
3	1	2
4	1	.
5	2	.
6	2	6
7	3	3
8	3	.
9	3	5
10	4	.
;
run;``````

so for N=4 the var column should fill with the value above (2) since both rows belong to group 1. However, for N=5, this should be left empty as it is a new group.

i tried the following code, but it is filling the cells with the value above regardless the group:

``````DATA want (DROP = X) ;
SET have ;
by group;
RETAIN X ;
IF NOT MISSING(var) THEN X = var ;
var = X ;
if missing(var) then delete;
RUN ;``````

So the output dataset should look like the following

``````N	Group	Var
1	1	3
2	1	6
3	1	2
4	1	2
5	2	.
6	2	6
7	3	3
8	3	3
9	3	5
10	4	.``````

Any help is appreciated

kind regards

Accepted Solutions
Solution
‎08-27-2017 12:45 AM
Super User
Posts: 7,929

## Re: Filling a cell with the value above by group

Use the FIRST. variable generated by the BY statement to figure out when you are starting a new by group.

``````data want ;
set a ;
by group ;
if first.group then want=var;
else want=coalesce(var,want);
retain want;
run;``````

All Replies
Posts: 1,147

## Re: Filling a cell with the value above by group

``````data want;
set a;
by group;
retain x;
if first.group then x=.;
if var ne . then x=var;
run;
``````
Thanks,
Jag
Solution
‎08-27-2017 12:45 AM
Super User
Posts: 7,929

## Re: Filling a cell with the value above by group

Use the FIRST. variable generated by the BY statement to figure out when you are starting a new by group.

``````data want ;
set a ;
by group ;
if first.group then want=var;
else want=coalesce(var,want);
retain want;
run;``````

☑ This topic is solved.