Help using Base SAS procedures

Remove Observations based on a SAS table

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Remove Observations based on a SAS table

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;


Accepted Solutions
Solution
‎05-09-2017 08:20 AM
Super User
Posts: 6,936

Re: Remove Observations based on a SAS table

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: Remove Observations based on a SAS table

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.

Solution
‎05-09-2017 08:20 AM
Super User
Posts: 6,936

Re: Remove Observations based on a SAS table

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 797

Re: Remove Observations based on a SAS table

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 194 views
  • 1 like
  • 4 in conversation