Hi all, I have a masterlist with 2 years of data... I need to segment data for some months and check it against the last 6 or 12 months of activity to mark how many are repeats vs net new What i have so far is this: Isolate the month i want. the list has a variable run_date which has the 1st day of a month as the run date. So jan = 1/1/2019 feb = 2/1/2019 PROC SQL; CREATE TABLE REPEAT_MONTH AS SELECT *,CASE WHEN COUNT(BAN) >1 THEN 'REPEAT' ELSE 'SINGLE' END AS REPS_IN_MONTH FROM MASTER WHERE RUN_DATE ='01Jul2019'd GROUP BY ID ORDER BY ID ; QUIT; PROC SORT DATA=REPEAT_MONTH nodupkey;by ban;quit; left join to the master table for the months i need to check against. in this case i need data from Jan 2019 to jun 2019, that is 6 months before run date specified above PROC SQL; CREATE TABLE historical AS SELECT A.*,B.RUN_DATE AS REPEAT_DATE,COUNT(A.BAN) AS COUNT, CASE WHEN COUNT(A.BAN) >1 THEN 'REPEAT' ELSE 'NEW' END AS IN_6M FROM REPEAT_MONTH A LEFT JOIN MASTER B ON A.id=B.id where 1 <= intck('month',b.run_date,a.run_Date,'c') <=6 GROUP BY A.id ORDER BY A.BAN,B.RUN_Date ; QUIT; doesn't give me the same yield as where b.run_date between '01jan2019'd and '01jun2019'd. what am i doing wrong. appreciate any thoughts on making any of this more efficient. Masterlist contains data from 2018 jan to 2020 mar. I'd like to run this for multiple months Thank you kindly
... View more