topic Re: Remove Observations based on a SAS table in SAS Procedures

Remove Observations based on a SAS table

GunnerEP — Wed, 03 May 2017 09:58:44 GMT

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;

Re: Remove Observations based on a SAS table

RW9 — Wed, 03 May 2017 10:27:12 GMT

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.

Re: Remove Observations based on a SAS table

Kurt_Bremser — Wed, 03 May 2017 11:22:12 GMT

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;

Re: Remove Observations based on a SAS table

mkeintz — Wed, 03 May 2017 15:40:00 GMT

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