Hi,
I want to do a conditional merge using proc SQL that:
data one;
input id name $;
datalines;
1 Tim
2 Tom
3 Beth
4 Harry
run;
data two;
input id treatment_code;
datalines;
1 1
1 2
2 1
2 4
2 9
3 11
4 2
run;
data want;
id name In_teatment
1 Tim "Yes"
2 Tom``"Yes"
3 Beth "No"
4 Harry "Yes"
;
Thanks! 🙂
Data step solution:
data one;
input id name $;
datalines;
1 Tim
2 Tom
3 Beth
4 Harry
;
run;
data two;
input id treatment_code;
datalines;
1 1
1 2
2 1
2 4
2 9
3 11
4 2
;
run;
data two_comp;
set two;
by id;
retain in_treatment ' ';
if first.id then in_treatment = 'No';
if treatment_code in (1,2) then in_treatment = 'Yes';
if last.id then output;
drop treatment_code;
run;
data want;
merge
one
two_comp
;
by id;
run;
SQL solution:
proc sql;
create table want as
select
a.id,
a.name,
(select
max(
case
when b.treatment_code in (1,2)
then 'Yes'
else 'No'
end
)
from two b
where a.id = b.id
group by b.id
) as in_treatment
from one a
;
quit;
Result from both:
id name in_treatment 1 Tim Yes 2 Tom Yes 3 Beth No 4 Harry Yes
Note how much better the code looks when using the "little running man" and {i} buttons for posting.
data one;
input id name $;
datalines;
1 Tim
2 Tom
3 Beth
4 Harry
;
run;
data two;
input id treatment_code;
datalines;
1 1
1 2
2 1
2 4
2 9
3 11
4 2
;
run;
proc sql;
select a.id,a.name,case when sum(treatment_code in (1,2)) >0 then 'Yes' else 'No ' end as want
from one as a left join two as b
on a.id=b.id
group by a.id,a.name;
quit;
data one;
input id name $;
datalines;
1 Tim
2 Tom
3 Beth
4 Harry
;
run;
data two;
input id treatment_code;
datalines;
1 1
1 2
2 1
2 4
2 9
3 11
4 2
;
run;
proc sql;
create table want as
select a.*,ifc(max(treatment_code in (1,2)),'YES','NO') as In_teatment
from one a left join two b
on a.id=b.id
group by a.id,name;
quit;
data one;
input id name $;
datalines;
1 Tim
2 Tom
3 Beth
4 Harry
;
run;
data two;
input id treatment_code;
datalines;
1 1
1 2
2 1
2 4
2 9
3 11
4 2
;
run;
data want;
merge one(in=a) two(in=b where=(treatment_code in (1,2)));
by id ;
if a;
if b then In_teatment='YES';
else In_teatment='NO';
if last.id;
run;
data one;
input id name $;
datalines;
1 Tim
2 Tom
3 Beth
4 Harry
;
run;
data two;
input id treatment_code;
datalines;
1 1
1 2
2 1
2 4
2 9
3 11
4 2
;
run;
proc sql;
create table want as
select distinct a.*,ifc(treatment_code>.,'YES','NO') as In_teatment
from one a left join two(where=(treatment_code in (1,2))) b
on a.id=b.id;
quit;
data one;
input id name $;
datalines;
1 Tim
2 Tom
3 Beth
4 Harry
;
run;
data two;
input id treatment_code;
datalines;
1 1
1 2
2 1
2 4
2 9
3 11
4 2
;
run;
data want ;
set one;
if _n_=1 then do;
if 0 then set two;
dcl hash H (dataset:'two',multidata:'y') ;
h.definekey ("id") ;
h.definedata ("treatment_code") ;
h.definedone () ;
end;
length In_teatment $3;
In_teatment='NO';
do while(h.do_over() eq 0);
if treatment_code in (1,2) then do;
In_teatment='YES';
leave;
end;
end;
run;
or
data want ;
set one;
if _n_=1 then do;
if 0 then set two;
dcl hash H (dataset:'two(where=(treatment_code in (1,2))',multidata:'y') ;
h.definekey ("id") ;
h.definedata ("id") ;
h.definedone () ;
end;
length In_teatment $3;
In_teatment='NO';
if h.check()=0 then In_teatment='YES';
drop treatment_code;
run;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.