hi i have two datasets A and B which could have common variable ID. I want to add a flag "Y" to the dataset A if the same ID existis in dataset B
example
A B
ID ID
1 2
2 Y 6
3 4
4 Y 10
data A;
input id;
cards;
1
2
3
4
;
run;
data B;
input id;
cards;
2
6
4
10
;
proc sql;
create table want as
select a.*,ifc(a.id=b.id,'Y',' ') as Flag
from a a left join b b
on a.id=b.id;
quit;
Hi,
try something like this:
data A;
input id;
cards;
1
2
3
4
;
run;
data B;
input id;
cards;
2
6
4
10
;
run;
data A;
if 0 then set A;
declare hash H(dataset:"B(keep=ID)");
H.DefineKey("ID");
H.DefineDone();
do until(eof);
set A end = eof;
if H.check()=0 then flag = "Y";
else flag = " ";
output;
end;
stop;
run;
All the best
Bart
Another solution: use a format:
data a;
input id $;
cards;
1
2
3
4
;
run;
data b;
input id $;
cards;
2
6
4
10
;
run;
data cntlin;
set b (rename=(id=start)) end=eof;
fmtname = 'lookup';
type = 'C';
label = 'Y';
output;
if eof
then do;
hlo = 'O';
start = 'OTHER';
label = ' ';
output;
end;
run;
proc format cntlin=cntlin;
run;
data want;
set a;
flag = put(id,$lookup.);
run;
data A; input id; cards; 1 2 3 4 ; run; data B; input id; cards; 2 6 4 10 ; run; proc sql; create table want as select *,exists(select * from b where id=a.id) as flag from a; quit;
data A;
input id;
cards;
1
2
3
4
;
run;
data B;
input id;
cards;
2
6
4
10
;
proc sql;
create table want as
select a.*,ifc(a.id=b.id,'Y',' ') as Flag
from a a left join b b
on a.id=b.id;
quit;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.