topic Re: How to find the rows for which consecutive columns have blank values? in SAS Programming

How to find the rows for which consecutive columns have blank values?

Shradha1 — Sun, 24 Oct 2021 08:11:49 GMT

I have a list of customers, and against each customer I have their monthly Spend for 13 consecutive months from M1 to M12.

Now, I want to select only those customers who have had zero spends for at least 6 or more consecutive months(inactive customers), and also the month from when their spend started reappearing post inactivity.

My dataset looks like this:

Customer	M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	M11	M12
A	.	94	63	106	424	252	499	356	435	469	200	423
B	.	.	.	.	.	.	.	13	137	440	75	99
C	67	118	364	.	.	.	.	.	.	156	40	415
D	430	423	.	.	.	.	54	165	26	477	129	411

So, from this dataset, I would select Customer B and C, as they have null spends for 6 or more consecutive months and for customer B, the month when spend restarted would be M8, and it will be M10 for Customer C.

So, my resultant dataset will look like this:

Customer	Month
B	M8
C	M10

How do I achieve this? Any help would be much appreciated.

Thanks a lot!

Re: How to find the rows for which consecutive columns have blank values?

Ksharp — Sun, 24 Oct 2021 10:48:33 GMT

data have;
infile cards expandtabs truncover;
input Customer	$ M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	M11	M12;
cards;
A	.	94	63	106	424	252	499	356	435	469	200	423
B	.	.	.	.	.	.	.	13	137	440	75	99
C	67	118	364	.	.	.	.	.	.	156	40	415
D	430	423	.	.	.	.	54	165	26	477	129	411
;

data want;
set have;
array x{*} M: ;
count=0;
do i=1 to dim(x);
 if missing(x{i}) then count+1;
  else do;
         count=0;
		 if _count>5 then month=vname(x{i});
	   end;
  _count=count;
end;
drop count _count i;
run;

Re: How to find the rows for which consecutive columns have blank values?

ErikLund_Jensen — Sun, 24 Oct 2021 11:03:19 GMT

Hi @Shradha1

It is good practise to supply example input data not as a table, but as a data step that can be run to create a sas data set, so anybody answering your post has something to work on.

I think in this case the best way is to use an array to hold all months 1-12 for a given costomer and then traverse the array and count consecutive missing months. If there are at least 6 consecutive missing months, then store the the month number where the costomer reappears, if this happens.

* Create test data ;
data have;
input Customer$ M1-M12;
cards;
A	.	94	63	106	424	252	499	356	435	469	200	423
B	.	.	.	.	.	.	.	13	137	440	75	99
C	67	118	364	.	.	.	.	.	.	156	40	415
D	430	423	.	.	.	.	54	165	26	477	129	411
;
run;

* Analyze;
data want (keep=Customer ReAppear); 
	set have;

	* Declare array containing 12 months and initiate counters;
	array spend M1-M12;
	Zerospend = 0;
	ReAppear = 0;

	* Loop over array, count consecutive missings and set reapprearing month;
	do i = 1 to 12;
		if missing(Spend{i}) then Zerospend = Zerospend + 1;
		else do;
			if Zerospend >= 6 then ReAppear = i;
			Zerospend = 0;
		end;
	end;

	* Output only if conditions are met;
	if ReAppear > 0 then output;
run;

The result is B 8 and C 10.

Re: How to find the rows for which consecutive columns have blank values?

Shradha1 — Mon, 25 Oct 2021 04:34:28 GMT

Thank you for the response! This helped a lot