How to determine whether a customer has skipped a month

[Giraffe123]

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.

[PaigeMiller]

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.

[Giraffe123]

Hi Paige

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

[PaigeMiller]

---

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

[utrocketeng · Accepted solution]

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;

[CarmineVerrell]

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;

[Ksharp]

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;