Long running proc sql query.

[SASUserRocks]

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;

[Tom · Accepted solution]

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

[SASKiwi]

Using DISTINCT all over the place will definitely slow your query. Are they all necessary?

[PGStats]

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.

[andreas_lds]

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.