comparing field values

[nirmalraj]

hi,

I have a field A in dataset 1 and field B in dataset 2.I want to compare the value of field A with field B and if it matches keep only field A values.Is there any method other than sort and merge and if statement because it is 2 different data sets even though in the same library.Can some one let me know if there is an alternate method.

Thanks
Nirmal

 

[klaz2002]

nirmalraj,
You can use Proc SQL to do what you want. I used dataset one & two for the example below. The code returns the full record of dataset one that matches the search.

proc sql;
create table Your_result as
select *
from one as a inner join two as b
on a.A =b.B;
quit;

I hope that this helps you.
Klaz

 

[ChrisW75]

Or:-

proc sql;
   create table Your_result as
   select a.*
   from one as a inner join two as b 
   on a.A =b.B;
quit;

to only select fields from a.
Or

proc sql;
  create table Your_result as
  select *
  from dset1
  where A in(select B from dset2)
   ;
quit;

This second method can be alot less efficient if there are a large number of records in the second dataset.
More than one way to skin a cat.

Another method I've seen used, which can be alot more efficient if dset1 is very large and dset2 is not so large is to do a logical delete (very handy if you are reporting on records deleted for auditing purposes) using DSET2 to create a format like this:-

data fmt1;
  set dset2(rename=(B=START)) end=last;


  retain hlo ' '
         fmtname '$del_flg';
  
  LABEL = 'N';
  
  output;

  if last then
    do;
      label = 'Y';
      hlo   = 'o';
      output;
    end;
run;

proc formt cntlin = fmt1;
run;

data dset1a;
  set dset1;

  delete_flag = put(A,$del_fmt.);
run;

It's a longer way of doing it, but it can run alot quicker under a number of circumstances, and also allows you to retain your "deleted" records.
NB if A and B are numerics, then drop the $ sign.