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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.