Filling a cell with the value above by group

Accepted Solution Solved
Reply
Regular Contributor
Posts: 169
Accepted Solution

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
Super User
Posts: 7,394

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;

image.png

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

Re: Filling a cell with the value above by group

PLease try 

 

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
Super User
Posts: 7,394

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;

image.png

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 419 views
  • 1 like
  • 3 in conversation