Remove Observations based on a SAS table

[GunnerEP]

Hi All,

I am writing a macro, which would remove records from my base dataset based on another table values(status), meaning

 status table:

id       emp_no  status

1        23333     expired

2       44444      active

3       66666      expired

Base table

id   emp_no   DOB               address

1    23333     26-Oct-1990   Street 1

2    44444    23-jan-1990     Street 2

3    66666     14-feb-1987   Street 3

Output table(required)

id emp_no   DOB               address

2    44444    23-jan-1990     Street 2

I have done it using merge, is there any more efficient way to achieve the same. ?

proc sql;

create table status as (select empno,status from status where status = 'expired');run;

data outputds;

merge base(in=a) status(in=b)

by emp_no;
if b then output expired;
else output outputds;
run;

[RW9]

Post test data in the form of a datastep!  This is the only way we can provide working code!

Also, I do not see any macro there?

You can simplfy your whole process down to one simple step:

proc sql;
  delete from BASE
  where EMP_NO in (select distinct EMP_NO from STATUS where STATUS="expired");
quit;

Or you can create table with more or less the same syntax if you want a new table.

[Kurt_Bremser · Accepted solution]

You can do it in one data step:

data
  expired
  outputds
;
merge
  base (in=a)
  status (
    in=b
    where=(status = 'expired')
  )
;
by emp_no;
if b
then output expired;
else output outputds;
run;

[mkeintz]

Instead of a macro, consider using a function, like the hashin() function below:

data status;
  input emp_no status :$8.;
datalines;
23333     expired
44444     active
66666     expired
run;
 
data base;
  input emp_no dob :date9. address :$&12.;
  format dob date9.;
datalines;
23333    26Oct1990   Street 1
44444    23jan1990   Street 2
66666    14feb1987   Street 3
run;
 
proc fcmp  outlib=work.temp.funcs;
  function hashin(emp_no);
    declare hash h (dataset:"mynames");
      rc=h.definekey("emp_no") ;
      rc=h.definedone();
  return (h.check()=0);
  endsub;
quit;

options cmplib=work.temp;

data mynames;
  set status;
  where status='active';
run;

data want;
  set base;
  where hashin(emp_no);
run;

This also makes sense when you don't really need to save the WANT dataset, but just need to use it once in a single PROC, as in:

proc freq data=base;

   where hashin(emp_no);

   tables a * b ;

run;

Also it's easy to get the complement:

proc freq data=base;

   where hashin(emp_no)=0;

   tables a * b;

run;

My sources are:

   Sample 47224: Load a SAS data set into a Hash Object using PROC FCMP

and

   Hashing in PROC FCMP to Enhance Your Productivity