Help using Base SAS procedures

By Group Processing

Reply
Frequent Contributor
Posts: 101

By Group Processing

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

Super User
Posts: 19,772

Re: By Group Processing

Posted in reply to RobertNYC

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;

Respected Advisor
Posts: 3,156

Re: By Group Processing

Posted in reply to RobertNYC

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

Super User
Posts: 19,772

Re: By Group Processing

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;

Ask a Question
Discussion stats
  • 3 replies
  • 266 views
  • 0 likes
  • 3 in conversation