Hi all—
I’m trying to get the hang of by group processing. How would you translate the SQL statement below into Base SAS using by group processing?
proc sql;
create table
last_clos as
select * from
EVENT_CLOSINGS (WHERE=(ACTIVE_FLAG = 'Y' ) )
group by
CASE_EVENTS_ID having EVENT_CLOSINGS_ID=max(EVENT_CLOSINGS_ID);
quit;
Thanks!!!
Are you sure that's what you want, basically the record where you only keep one ID, which is the highest out of all records?
Usually the group by variable isn't the same as the max(variable) though I can see where it might be.
It's a few steps in BASE SAS, so SQL is probably the best for this actually.
Proc sort data=event_closings out=ec2;
where active_flag='Y';
by descending case_events_id;
run;
data ec3;
set ec2;
by descending case_events_id;
if first.case_events_id then group=1;
else group=group+1;
run;
data last_clos;
set event_closings;
where group=1;
run;
Hi,
I agree with Fareeza, this looks like SQL job. If there are NO "EVENT_CLOSINGS_ID" duplicates within same "EVENT_CLOSINGS_ID", then it is not too bad, you will need two step, 1)sort 2) output;
Proc sort data=event_closings out=have;
where active_flag='Y';
by CASE_EVENTS_ID EVENT_CLOSINGS_ID ;
run;
data want;
set have;
if last.CASE_EVENTS_ID;
run;
However, if you have those duplicates, then the job becomes more complicated if you want to mimic what SQL does completely:
data want;
if _n_=1 then do;
if 0 then set have;
dcl hash h(dataset: 'have', multidata:'y');
h.definekey('CASE_EVENTS_ID', 'EVENT_CLOSINGS_ID');
h.definedata(all:'y');
h.definedone();
end;
set have;
by CASE_EVENTS_ID EVENT_CLOSINGS_ID ;
if last.CASE_EVENTS_ID then do;;
_rc=h.find();
do _rc=0 by 0 while (_rc=0);
output;
_rc=h.find_next();
end;
end;
drop _rc;
run;
Haikuo
You can do it without hash as well, however I did it incorrectly and it is a sort and two data steps.
data ec3;
set ec2;
by descending case_events_id;
if _n_=1 then group=1;
else if first.case_events_id then group+1;
run;
data last_close;
set ec3;
where group=1;
run;
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.