BookmarkSubscribeRSS Feed
RobertNYC
Obsidian | Level 7

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

3 REPLIES 3
Reeza
Super User

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;

Haikuo
Onyx | Level 15

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

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 837 views
  • 0 likes
  • 3 in conversation