topic By Group Processing in SAS Procedures

By Group Processing

RobertNYC — Tue, 31 Jul 2012 21:41:52 GMT

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!!!

Re: By Group Processing

Reeza — Tue, 31 Jul 2012 22:02:35 GMT

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;

Re: By Group Processing

Haikuo — Tue, 31 Jul 2012 22:52:57 GMT

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;

drop _rc;

run;

Haikuo

Re: By Group Processing

Reeza — Tue, 31 Jul 2012 23:01:11 GMT

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;