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;
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;
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.
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.