Hello,
I have a data set as follows:
username values
AAA .
AAA .
AAA 5
AAA .
AAA .
BBB 1
BBB .
BBB .
CCC .
DDD .
DDD 5
i would like to replace the missing values for each username with its non null one, in other way i would like to obtain something like this:
Usernames values
AAA 5
AAA 5
AAA 5
AAA 5
AAA 5
BBB 1
BBB 1
BBB 1
CCC .
DDD 5
DDD 5
Do u have any suggestion guys ?
Thank you in advance for ur help
What happens if more than one row for username has a number? Also, please provide test data in the form of a datastep, as such this is untested:
proc sql; create table want as select a.username, coalesce(a.values,b.values) as values from have a left join (select distinct username,values from have where values ne .) b on a.username=b.username; quit;
What happens if more than one row for username has a number? Also, please provide test data in the form of a datastep, as such this is untested:
proc sql; create table want as select a.username, coalesce(a.values,b.values) as values from have a left join (select distinct username,values from have where values ne .) b on a.username=b.username; quit;
Thank u so much, this is exactly what i wanted
Have a nice journey
data have;
input username $ values ;
cards;
AAA .
AAA .
AAA 5
AAA .
AAA .
BBB 1
BBB .
BBB .
CCC .
DDD .
DDD 5
;
data want;
merge have(drop=values) have(where=(values is not missing));
by username;
run;
This solution works good also, thank you for your help
@Ksharp's merge solution is terse and sweet, but it relies on the input data set's sorted order.
@RW9's SQL solution is more universal since it doesn't rely on any order.
Methought it'd be nice to augment these fine pieces of coding with a DATA step approach that doesn't need the input to be in order, either:
data want (drop = _:) ;
if _n_ = 1 then do ;
dcl hash h (dataset:"have", multidata:"y") ;
h.definekey ("username") ;
h.definedata ("values") ;
h.definedone () ;
end ;
set have ;
if h.find() = 0 then do until (h.find_next() ne 0) ;
if not missing (values) then _v = values ;
end ;
values = _v ;
run ;
Note that the DO loop following SET is coded in such a way that it will work in both 9.3 and 9.4. In 9.4 and up, it can be coded simpler:
do while (h.do_over() = 0) ;
if not missing (values) then _v = values ;
end ;
Finally, if the input is sorted, I'd personally prefer @Ksharp's merge. Just for the record, this kind of processing nicely yields to the intrinsic structure of the double DoW loop:
data want (drop = _:) ;
do _n_ = 1 by 1 until (last.username) ;
set have ;
by username ;
if not missing (values) then _v = values ;
end ;
do _n_ = 1 to _n_ ;
set have ;
values = _v ;
output ;
end ;
run ;
Paul D.
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.