counting in a colums then + 7 between numbers

[Rebekka]

Hallo good people,

I need help again; I have now used too much time to try to find out how to do this. Please help! As new in SAS, I cant find the answer myself.

I have pnr and col1 and want to make Count or count1 or something similar.

In col1 there are +7 between dates (DDMMYY10.) that I want to count. Then the date change with more (or less then + 7) I would like to make a new count until there again are more or less then + 7 between the two dates.

pnr	Col1	count	Count1
123	30/12/2013		1
123	06/01/2014	2	2
158	14/03/2011		1
158	21/03/2011		2
158	28/03/2011		3
158	04/04/2011		4
158	11/04/2011		5
158	18/04/2011	6	6
234	24/09/2007		1
234	01/10/2007		2
234	08/10/2007		3
234	15/10/2007	4	4
234	08/12/2014	1	1

Is this possible?

Count could also look like Count1 or another way that will show the count with the different pnr.

Pnr are characters, col1 are numbers.and I use SAS 9.4

Thank you so much and please ask if you need more information 🙂

Rebekka

[Reeza]

• DIF() to calculate diff
• BY/FIRST to identify the first of a group
• RETAIN to hold values
• SUM statement

Those are the tools you need. 

If you've made attempts, please post any code you have so far.

---

@Rebekka wrote:

Hallo good people,

I need help again; I have now used too much time to try to find out how to do this. Please help! As new in SAS, I cant find the answer myself.

I have pnr and col1 and want to make Count or count1 or something similar.

In col1 there are +7 between dates (DDMMYY10.) that I want to count. Then the date change with more (or less then + 7) I would like to make a new count until there again are more or less then + 7 between the two dates.

pnr	Col1	count	Count1
123	30/12/2013		1
123	06/01/2014	2	2
158	14/03/2011		1
158	21/03/2011		2
158	28/03/2011		3
158	04/04/2011		4
158	11/04/2011		5
158	18/04/2011	6	6
234	24/09/2007		1
234	01/10/2007		2
234	08/10/2007		3
234	15/10/2007	4	4
234	08/12/2014	1	1

Is this possible?

Count could also look like Count1 or another way that will show the count with the different pnr.

Pnr are characters, col1 are numbers.and I use SAS 9.4

Thank you so much and please ask if you need more information 🙂

Rebekka

---

[Ksharp]

Please post data step, nobody would like to type it for you.

data temp;
 set have;
 by pnr;
 dif=dif(col1);
 if first.pnr then dif=0;
run;

data want;
 set temp;
 by pnr;
 if dif<=7 or first.pnr then group+1;
run;

data want1;
 set want;
 by group;
 if first.group then count1=0;
 count1+1;
run;

[SuryaKiran]

You can get your count1 column by reading the previous col1 values using the function DIF or LAG, but to get the count values you need to read the next values, which is not possible because data is processed sequentially one record after the other. This can be achieved by using the MERGE with firstobs=2. 

Check this code:

data have;
format col1 mmddyy10.;
infile datalines dlm=" ";
input pnr col1 ddmmyy10.;
datalines;
123 30/12/2013
123 06/01/2014
158 14/03/2011
158 21/03/2011
158 28/03/2011
158 04/04/2011
158 11/04/2011
158 18/04/2011
234 24/09/2007
234 01/10/2007
234 08/10/2007
234 15/10/2007
234 08/12/2014
234 15/12/2014
;
run;

proc sort data=have;
by pnr col1;
run;

DATA want(drop=dif);
retain count1;
set have;
by pnr col1;
dif=dif(col1);
if first.pnr then do;
			count1=1;
			dif=0;
			end;
else if dif<=7 then count1+1;
else count1=1;
RUN;

DATA want_2(Drop=pnr_next count1_next);
merge want want(firstobs=2 keep=pnr count1 rename=(pnr=pnr_next count1=count1_next));
if count1>count1_next then count=count1;
run;

[Tom · Accepted solution]

Seems pretty simple.

data want ;
  set have ;
  by pnr ;
  if first.pnr or dif(col1)>7 then newcol=1;
  else newcol+1;
run;

Obs    pnr          Col1    count    Count1    newcol

  1    123    2013-12-30      .         1         1
  2    123    2014-01-06      2         2         2
  3    158    2011-03-14      .         1         1
  4    158    2011-03-21      .         2         2
  5    158    2011-03-28      .         3         3
  6    158    2011-04-04      .         4         4
  7    158    2011-04-11      .         5         5
  8    158    2011-04-18      6         6         6
  9    234    2007-09-24      .         1         1
 10    234    2007-10-01      .         2         2
 11    234    2007-10-08      .         3         3
 12    234    2007-10-15      4         4         4
 13    234    2014-12-08      1         1         1

[Rebekka]

Hi Tom,

it work so perfect! I knew that the answer was simple but couldnt come up with it my self!

Thánks Rebekka