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
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.
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.
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
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;
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;
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.
Agreeing with @novinosrin , in the long run 0s and 1s are better than Y and N.
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;
The ID variable is character variable so i cannot use best12. format
Oh I took your sample as-is.
Please let us know which ones are char and num. Thanks!
all vaariables are characters in these cases
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.
@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.
all the variables i mentioned in the example are character variables
@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.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.