BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GunnerEP
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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