Extract records 6 months prior to a date

TheNovice — Fri, 01 May 2020 21:08:00 GMT

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

Re: Extract records 6 months prior to a date

ballardw — Fri, 01 May 2020 22:33:47 GMT

You may want to examine the dates that the month difference changes in the below code. Month1 does not use the 'C' option in intck and Month2 does.

data example;
   do date = '31DEC2018'd to '01Jul2019'd;
      months1 = intck('month',date,'01Jul2019'd);
      months2 = intck('month',date,'01Jul2019'd,'C');
      output;
   end;
   format date date9.;
run;

It may be all you need to do is drop the 'C' from intck function call.

topic Re: Extract records 6 months prior to a date in SAS Programming

Extract records 6 months prior to a date

Re: Extract records 6 months prior to a date