topic Re: Filling a cell with the value above by group in SAS Programming

Filling a cell with the value above by group

ammarhm — Sun, 27 Aug 2017 01:18:29 GMT

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

Re: Filling a cell with the value above by group

Jagadishkatam — Sun, 27 Aug 2017 02:13:30 GMT

PLease try

data want;
set a;
by group;
retain x;
if first.group then x=.;
if var ne . then x=var;
run;

Re: Filling a cell with the value above by group

Tom — Sun, 27 Aug 2017 02:14:22 GMT

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;