topic Re: how to check if same id is present in 2 different datasets and flag them if it is same in SAS Programming

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

vraj1 — Thu, 22 Aug 2019 13:36:23 GMT

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

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

PaigeMiller — Thu, 22 Aug 2019 13:42:49 GMT

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.

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

vraj1 — Thu, 22 Aug 2019 13:45:05 GMT

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

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

novinosrin — Thu, 22 Aug 2019 14:02:49 GMT

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;

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

novinosrin — Thu, 22 Aug 2019 14:05:02 GMT

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;

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

PaigeMiller — Thu, 22 Aug 2019 14:08:03 GMT

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;

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

PaigeMiller — Thu, 22 Aug 2019 14:08:47 GMT

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

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

vraj1 — Thu, 22 Aug 2019 14:13:37 GMT

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.

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

vraj1 — Thu, 22 Aug 2019 14:16:32 GMT

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

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

novinosrin — Thu, 22 Aug 2019 14:18:22 GMT

Oh I took your sample as-is.

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

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

PaigeMiller — Thu, 22 Aug 2019 14:18:47 GMT

@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.

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

vraj1 — Thu, 22 Aug 2019 14:19:39 GMT

all vaariables are characters in these cases

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

vraj1 — Thu, 22 Aug 2019 14:25:47 GMT

all the variables i mentioned in the example are character variables

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

PaigeMiller — Thu, 22 Aug 2019 14:28:23 GMT

@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.

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

vraj1 — Thu, 22 Aug 2019 14:29:42 GMT

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;

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

PaigeMiller — Thu, 22 Aug 2019 14:32:57 GMT

So way back in message 9 of this thread, you replied to me, but this is not my code. So that's why I was very unclear about what you were saying.

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

novinosrin — Thu, 22 Aug 2019 14:35:54 GMT

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.

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

Ksharp — Fri, 23 Aug 2019 12:47:36 GMT

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,
;;;;

data want;
 if _n_=1 then do;
   if 0 then set book11;
   declare hash L(dataset:'book11(keep=LL_1 where=(LL_1 is not missing))');
   L.definekey('LL_1');
   L.definedone();
   declare hash M(dataset:'book11(keep=MM_2 where=(MM_2 is not missing))');
   M.definekey('MM_2');
   M.definedone();
 end;
set main;
if L.check(key:id)=0 then LL_Flag='Y';else LL_Flag='N';
if M.check(key:id)=0 then MM_Flag='Y';else MM_Flag='N';
drop LL_1 MM_2;
run;