proc sql;
create table dashare.Total_Click_Impressions_Lead4 as
select
distinct A.date,
A.Product,
count( distinct A.ImpressionId) as Impressions,
count( distinct B.clickid) as Clicks,
count(distinct row_id||x_adcb_prod_group||adcb_utm_camp||utm_medium) as Leads,
startdate as start_date ,enddate as end_date
from
impressions A
left join
clicks B
on A.date = B.date and
A.Product = B.ProductLabel
left join (
select
row_id,x_adcb_prod_group,adcb_utm_camp,utm_medium,x_adcb_last_lead_action_date
from
DAshare.HYPERPERSONALIZATION_LEADS
where
x_adcb_last_lead_action_date = '09Aug2021'd
) C
on A.date = C.x_adcb_last_lead_action_date and
A.Product = C.adcb_utm_camp
left join (
select
min(date) format date11. as startdate,
max(date) format date11. as enddate,
product
from
dashare.Total_Click_Impressions_Lead
group by
product having max(date) >='09Aug2021'd
) D
on A.Product = D.Product
group by A.date, A.Product;
quit;
Is there a question here?
Are you looking for ways to speed it up?
You might see if it helps to summarize IMPRESSION before joining with the other things:
from (date,product, count(distinct impressionid) as impressions from impressions
group by date,product) A
Is there a question here?
Are you looking for ways to speed it up?
You might see if it helps to summarize IMPRESSION before joining with the other things:
from (date,product, count(distinct impressionid) as impressions from impressions
group by date,product) A
Using DISTINCT all over the place will definitely slow your query. Are they all necessary?
Also, including startdate and enddate in your select clause but not in your group by clause will force remerging on the counts with the original data (you should get a NOTE about that in the log). Is that intended? If not, you should include startdate and enddate in the group by clause. That would speed things up a bit.
This topic seems to continue https://communities.sas.com/t5/SAS-Programming/Proc-sql-query-optimization-Running-long-hours/m-p/76...
Posting log with debugging options enabled and sample data could help use finding ways to improve the code.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.