how to check if same id is present in 2 different datasets and flag them if it is same

[vraj1]

I have 2 datasets one main dataset and i need to check with the other dataset and if the id is same then flag as Y.

Below is the example: any help on doing in one step

data WORK.main;
infile datalines dsd truncover;
input ID:BEST12.;
datalines4;
111
134
122
221
232
198
908
762
212
2122
3435
2123
;;;;

second dataset

data WORK.BOOK11;
infile datalines dsd truncover;
input LL_1:BEST12. MM_2:BEST12.;
datalines4;
111,908
134,762
122,212
221,
232,
198,
;;;;

I need the final dataset like below:

ID	MM_Flag	LL_Flag
111	N	Y
134	N	Y
122	N	Y
221	N	Y
232	N	Y
198	N	Y
908	Y	N
762	Y	N
212	Y	N
2122	N	N
3435	N	N
2123	N	N

any help in how to solve this please

[PaigeMiller]

I think we need more detailed information from you on how the final table is created from the original data sets.

In the first data set — are these LL or MM flags?

In the second data set, it's not clear to me how you are using the two different columns, LL_1 and MM_2.

[vraj1]

In the first dataset it is named as ID and in the second there are 2 variables called LL_ and MM_ which has same id's which are on the first one. I need to merge and see if the ID is matching with LL_ then have a flag LL_fag='Y' for that id and the same with the other one.

Hope this explains

[novinosrin]

Hi @vraj1  

IMHO, It's better to keep Y and N rather as 1's and 0's. Always easy to crunch or play with numbers and formatting the same as characters if you want.

data WORK.main;
infile datalines dsd truncover;
input ID:BEST12.;
datalines4;
111
134
122
221
232
198
908
762
212
2122
3435
2123
;;;;

data WORK.BOOK11;
infile datalines dsd truncover;
input LL_1:BEST12. MM_2:BEST12.;
datalines4;
111,908
134,762
122,212
221,
232,
198,
;;;;
proc sql;
create table want as
select id, max(indexw(put(b.MM_2,best12.),put(id,best12.))>0) as MM_Flag,max(indexw(put(b.LL_1,best12.),put(id,best12.))>0) as LL_Flag
from main a,BOOK11 b
group by id;
quit;

[novinosrin]

Okay @vraj1   Just in case, if you may not want to take my opinion as still want the values as 'Y and 'N'

proc sql;
create table want as
select id, ifc(max(indexw(put(b.MM_2,best12.),put(id,best12.))>0),'Y','N') as MM_Flag,ifc(max(indexw(put(b.LL_1,best12.),put(id,best12.))>0),'Y','N') as LL_Flag
from main a,BOOK11 b
group by id;
quit;

[vraj1]

I get numeric formats error if i use best12.

ERROR: Numeric format BEST in PUT function requires a numeric argument.
ERROR: Numeric format BEST in PUT function requires a numeric argument.
ERROR: Numeric format BEST in PUT function requires a numeric argument.
ERROR: Numeric format BEST in PUT function requires a numeric argument.

[PaigeMiller]

Agreeing with @novinosrin , in the long run 0s and 1s are better than Y and N.

[PaigeMiller]

data WORK.main;
infile datalines dsd truncover;
input ID:BEST12.;
datalines4;
111
134
122
221
232
198
908
762
212
2122
3435
2123
;;;;


data LL(keep=ll_1) MM(keep=mm_2);
infile datalines dsd truncover;
input LL_1:BEST12. MM_2:BEST12.;
if not missing(ll_1) then output LL;
if not missing(mm_2) then output mm;
datalines4;
111,908
134,762
122,212
221,
232,
198,
;;;;

proc sort data=main; by id; run;
proc sort data=ll; by ll_1; run;
proc sort data=mm; by mm_2; run;

data combine;
    merge main(in=in1) ll(in=in2 rename=(ll_1=id)) mm(in=in3 rename=(mm_2=id));
	by id;
	if in1 and in3 then mm_flag='Y';
	if in1 and in2 then ll_flag='Y';
	if ll_flag^='Y' then ll_flag='N';
	if mm_flag^='Y' then mm_flag='N';
run;

[vraj1]

The ID variable is character variable so i cannot use best12. format

[novinosrin]

Oh I took your sample as-is.

Please let us know which ones are char and num. Thanks!

[vraj1]

all vaariables are characters in these cases

[novinosrin · Accepted solution]

Okay @vraj1  In that case all you need is

proc sql;
create table want as
select id, ifc(max(indexw(MM_2,id)>0),'Y','N') as MM_Flag,ifc(max(indexw(LL_1,id)>0),'Y','N') as LL_Flag
from main a,BOOK11 b
group by id;
quit;

Sir @PaigeMiller  Methinks the OP is confused with the concept of formats from the pursuant of the format question. Anyways, it's not a concern for us.

[PaigeMiller]

---

@vraj1 wrote:

The ID variable is character variable so i cannot use best12. format

---

It's unclear exactly what you are referring to via this comment.

[vraj1]

all the variables i mentioned in the example are character variables

[PaigeMiller]

---

@vraj1 wrote:

all the variables i mentioned in the example are character variables

---

It is not clear what you are referring to via this comment ... show us the part of the code you are referring to ... show us the error in your SAS log, where you provide the entire SAS log and not just the error messages.