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
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;
PLease try
data want;
set a;
by group;
retain x;
if first.group then x=.;
if var ne . then x=var;
run;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.