summary stat via proc sql-slow run

[Ronein]

Hello

I want to calculate for each "agreement_ID" an indicator (1/0) .

The "have" data set is very big and it takes very long time to run this query.

My question-Can you suggest other code to run it more quickly?

proc sql;
create table want  as
select Agreement_Id,
       max(case when compress(Event_Code) in ('M14','M15') then 1 else 0 end ) as Ind_artifical_transaction
from have
where Event_Date>='01JAN2020'd
group by Agreement_Id
;
quit;

[SASKiwi]

Is HAVE a SAS table or an external database table?

Try this as an experiment. It removes the overhead of the CASE logic:

proc sql;
create table want  as
select Agreement_Id,
       count(*) as row_count
from have
where Event_Date>='01JAN2020'd
group by Agreement_Id
;
quit;

How long does this run for compared to your original code?

[Ronein]

But you didnt give any attention to compress(Event_Code) in ('M14','M15')

[SASKiwi]

My test removes the CASE logic, to see how long it takes. It is meant to test if CASE is what is slowing your query, not to provide a final solution.

[PaigeMiller]

Use PROC SUMMARY

[Ronein]

May you please show the code?

How do you count observations with related to 'M14','M15'?

[Kurt_Bremser]

Try this:

proc sort
  data=have (
    keep=agreement_id event_code event_date
    where=(event_date = '01jan202'd)
  )
  out=sorted (drop=event_date)
;
run;

data want;
set sorted;
by agreement_id;
retain ind_artificial_transaction;
if first.agreement_id then ind_artificial_transaction = 0;
if event_code in ('M14','M15') then ind_artificial_transaction = 1;
if last.agreement_id;
keep agreement_id ind_artificial_transaction;
run;

[Ronein]

You forgot add by agreement_id; to proc sort

[Kurt_Bremser]

---

@Ronein wrote:
You forgot add by agreement_id; to proc sort

---

Correct 😉

[Ksharp]

/*
Can you post some data to test your code ?
*/

proc sql;
create table temp  as
select distinct Agreement_Id,1 as Ind_artifical_transaction
from have
where Event_Date>='01JAN2020'd and compress(Event_Code) in ('M14','M15')
;

create table want as
select * from temp
union
select distinct Agreement_Id,0
from have
where Event_Date>='01JAN2020'd and Agreement_Id not in (select Agreement_Id from temp)
;
quit;