topic Sas query optimization in New SAS User

Sas query optimization

SASUserRocks — Thu, 14 Jul 2022 11:31:00 GMT

Dear Friends,

Need some help. Unable to retrieve results as expected with below query.

proc sql;
create table dashare.Push_aggregated_table as
select
c.product,
OFFERSTARTDATE as startdate format date11. ,
OFFERENDDATE as enddate format date11. ,
CAMPAIGNTYPE,
a.Impressions as Imp,
b.Clicks as Clicks
from (
select INSERTDATETIME,PID,count(distinct PID) as Impressions from IMPRESSIONS_DATA where substr(PID,18,10)='C000001934'
group by PID) a
left join
(
select INSERTDATETIME,PID,count(distinct PID) as Clicks from CLICKS_DATA where substr(PID,18,10)='C000001934'
group by PID,INSERTDATETIME
) b
on a.PID=b.pid
inner join
(select min(OFFERSTARTDATE) as OFFERSTARTDATE,max(OFFERENDDATE) as OFFERENDDATE,product,CAMPAIGNTYPE,TREATMENTCODE ,CAMPAIGNCODE from CONTACTHISTORY where CHANNEL = 'Push Notification'
group by CAMPAIGNCODE,TREATMENTCODE
) c
on substr(a.PID,18,10)=c.CAMPAIGNCODE and substr(a.PID,29,10)= c.TREATMENTCODE
group by product,a.PID;
run;

Re: Sas query optimization

PaigeMiller — Thu, 14 Jul 2022 11:50:09 GMT

@SASUserRocks wrote:

Need some help. Unable to retrieve results as expected with below query.

Explain further. What happens? Is there an error message?

If so, show us the ENTIRE log for this PROC SQL.

Re: Sas query optimization

andreas_lds — Thu, 14 Jul 2022 12:11:16 GMT

Hardly possible to help, because i don't know what the expected result is. So you need to explain further.

Re: Sas query optimization

ballardw — Thu, 14 Jul 2022 16:22:15 GMT

Examples of the 3 data sets used, as data step code so we can recreate them and test your code.

Then what the expected result should be for the provided example. (Implies make the sets small enough you know what the result should be).

Possibly a description of exactly how the results aren't matching your expectations.

Re: Sas query optimization

PGStats — Thu, 14 Jul 2022 17:23:56 GMT

At first sight, the only thing suspicious to me in your query is

select ...., PID, count(distinct PID) .... from .... group by PID

if you group by PID, then count(distinct PID) will be one, by definition.

Also note that the first subquery will require remerging because you do not group by INSERTDATETIME. This will likely generate more records than you expect. Either remove INSERTDATETIME from the select list or add INSERTDATETIME to the group by clause.