Hi,
I am trying to create the dummy variable Want which is = 1 if there is any observation that has a value in Have.
E.g. Want = 1 for all observations of A1 because there is at least one observation with a value in Have. Want = 0 for B1.
Is there a way to code this? Thanks for your help!
Sample data:
ID | Have | Want |
A1 | . | 1 |
A1 | . | 1 |
A1 | 100 | 1 |
A1 | . | 1 |
A1 | . | 1 |
A1 | . | 1 |
A1 | . | 1 |
A1 | . | 1 |
B1 | . | 0 |
B1 | . | 0 |
B1 | . | 0 |
B1 | . | 0 |
B1 | . | 0 |
B1 | . | 0 |
B1 | . | 0 |
B1 | . | 0 |
B1 | . | 0 |
B1 | . | 0 |
Regards,
Daniel
data have ;
id = 'A1' ; have = . ; output ;
id = 'A1' ; have = 100 ; output ;
id = 'A1' ; have = . ; output ;
id = 'B1' ; have = . ; output ;
id = 'B1' ; have = . ; output ;
id = 'B1' ; have = . ; output ;
run ;
proc sql ;
create table want as
select have.*,
case when id in
(select distinct id
from have
where have is not missing) then 1 else 0 end as want
from have ;
quit ;
Hi, here's a data step solution ...
data want;
want = 0;
do until (last.id);
set have (in=one) have;
by id;
if one and have then want = 1;
if ^one then output;
end;
run;
It's based on Howard Schreier's idea of a "self-interleave" ...
Interleaving a Dataset with Itself: How and Why
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.