topic Re: summary stat via proc sql-slow run in SAS Programming

summary stat via proc sql-slow run

Ronein — Thu, 23 Mar 2023 20:41:31 GMT

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;

Re: summary stat via proc sql-slow run

SASKiwi — Thu, 23 Mar 2023 20:55:31 GMT

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?

Re: summary stat via proc sql-slow run

PaigeMiller — Thu, 23 Mar 2023 21:03:51 GMT

Use PROC SUMMARY

Re: summary stat via proc sql-slow run

Ronein — Thu, 23 Mar 2023 21:09:36 GMT

May you please show the code?

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

Re: summary stat via proc sql-slow run

Kurt_Bremser — Thu, 23 Mar 2023 21:20:45 GMT

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;

Re: summary stat via proc sql-slow run

Ronein — Fri, 24 Mar 2023 10:42:55 GMT

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

Re: summary stat via proc sql-slow run

Ronein — Fri, 24 Mar 2023 10:52:12 GMT

You forgot add by agreement_id; to proc sort

Re: summary stat via proc sql-slow run

Kurt_Bremser — Fri, 24 Mar 2023 11:13:10 GMT

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

Correct 😉

Re: summary stat via proc sql-slow run

Ksharp — Fri, 24 Mar 2023 11:58:29 GMT

/*
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;

Re: summary stat via proc sql-slow run

SASKiwi — Sun, 26 Mar 2023 22:53:50 GMT

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.