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;
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
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.
Ready to level-up your skills? Choose your own adventure.