BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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;
9 REPLIES 9
SASKiwi
PROC Star

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
Meteorite | Level 14
But you didnt give any attention to compress(Event_Code) in ('M14','M15')
SASKiwi
PROC Star

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
Diamond | Level 26

Use PROC SUMMARY

--
Paige Miller
Ronein
Meteorite | Level 14

May you please show the code?

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

Kurt_Bremser
Super User

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
Meteorite | Level 14
You forgot add by agreement_id; to proc sort
Ksharp
Super User
/*
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 915 views
  • 1 like
  • 5 in conversation