Sas query optimization

[SASUserRocks]

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;

[PaigeMiller]

---

@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.

[andreas_lds]

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

[ballardw]

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.

[PGStats]

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.