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;