topic Re: How to determine whether a customer has skipped a month%3F in SAS Programming

How to determine whether a customer has skipped a month

Giraffe123 — Tue, 02 Feb 2021 14:23:03 GMT

Hi All,

My question is how do I determine whether a customer does not appear in a month? For example:

Month	Customer ID
Jan	a
Jan	b
Feb	a
Feb	b
Mar	a
Apr	a
Apr	b
May	a
May	b

For instance, how do I determine that customer "b" skipped March?

Thanks for your help.

Re: How to determine whether a customer has skipped a month%3F

PaigeMiller — Tue, 02 Feb 2021 12:44:13 GMT

Is that exactly how your data appears, where month is a 3-letter abbreviation? This is a very poor way to represent month.

Make your life much simpler, change month from a 3-letter character string to an actual numeric SAS date, and then the problem is simple. Otherwise, you have to write a program that knows Apr comes AFTER Mar (and all the other combinations), and also arranging data with 3-letter abbreviations utterly fails at year boundaries.

Re: How to determine whether a customer has skipped a month%3F

Giraffe123 — Tue, 02 Feb 2021 12:52:59 GMT

Hi Paige

Thanks for your quick reply. My month variable is in "date" format, sorry for not including that in my question.

Re: How to determine whether a customer has skipped a month%3F

utrocketeng — Tue, 02 Feb 2021 13:02:19 GMT

not having your data, i think this code ought to work. you may need to tweak it a bit.

PROC SQL;
CREATE TABLE WORK.Want AS
SELECT b.*
FROM
	work.have a
	FULL OUTER JOIN 
	(
		SELECT *
		FROM (SELECT DISTINCT Month FROM work.have) a
		CROSS JOIN (SELECT DISTINCT CustomerID FROM work.have) between
	) b	 
WHERE
	a.CustomerID is null
;
QUIT;

Re: How to determine whether a customer has skipped a month%3F

CarmineVerrell — Tue, 02 Feb 2021 13:03:01 GMT

Thanks for the question, normally we would want you as a customer to be able to do this yourself. I would highly recommend taking the some more Advanced SAS training. In this case I would suggest a SAS Programming 3 course. Obviously, if you have taken the SAS Programming 2 course first.

data customer;
infile cards;
input Month $ Customer $;

cards;
Jan a
Jan b
Feb a
Feb b
Mar a
Apr a
Apr b
May a
May b
;

run;

proc sort data=customer out=customersorted;
by customer;
run;

data customernotinmonths(keep=customer month);
set customersorted end=eof;
by customer ;
Array Months(12) $ ("Jan" "Feb" "Mar" "Apr" "May" "Jun" "Jul" "Aug" "Sep" "Oct" "Nov" "Dec");
Array mthchk(12);
Retain mthchk1-mthchk12;

if first.customer then do;
do i=1 to dim(mthchk);
mthchk(i)=0;
end;
end;

do j=1 to dim(months);
if month=months(j) then mthchk(j)=1;
end;

if last.customer then do;
do x=1 to dim(mthchk);
if mthchk(x)=0 then
do;
month=months(x);
output;
end;
end;
end;

run;

Re: How to determine whether a customer has skipped a month%3F

PaigeMiller — Tue, 02 Feb 2021 13:07:41 GMT

@Giraffe123 wrote:
Hi Paige

Thanks for your quick reply. My month variable is in "date" format, sorry for not including that in my question.

Not sure what this means. Does this mean that month is a numeric value which is a true SAS date (which is the number of days since 1-1-1960)?

You will get faster and better answers when you provide the data in the form it exists in your data sets, rather than 3-letter abbreviations which is not how the data exists in your data sets. Can you provide the data following these instructions?

Re: How to determine whether a customer has skipped a month%3F

Ksharp — Tue, 02 Feb 2021 13:15:32 GMT

Do you need consider YEAR ? better post your real data,not just format value .

data have;
infile cards expandtabs truncover;
input Month : monyy7.	CustomerID $;
format month monyy7.;
cards;
Jan2010	a
Jan2010	b
Feb2010	a
Feb2010	b
Mar2010	a
Apr2010	a
Apr2010	b
May2010 a
May2010	b
;

proc sql;
create table want as
select b.*,not missing(a.month) as is_in_have
 from have as a right join
 (select * from (select distinct month from have),(select distinct CustomerID from have)) as b
 on a.month=b.month and a.CustomerID=b.CustomerID;
quit;