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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.