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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: