Solved
New Contributor
Posts: 4

# counting in a colums then + 7 between numbers

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

Rebekka

Accepted Solutions
Solution
‎03-16-2018 04:23 PM
Super User
Posts: 8,093

## Re: counting in a colums then + 7 between numbers

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```

All Replies
Super User
Posts: 23,713

## Re: counting in a colums then + 7 between numbers

• 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

Rebekka

Super User
Posts: 10,778

## Re: counting in a colums then + 7 between numbers

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;``````
Valued Guide
Posts: 590

## Re: counting in a colums then + 7 between numbers

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;
``````

Thanks,
Suryakiran
Solution
‎03-16-2018 04:23 PM
Super User
Posts: 8,093

## Re: counting in a colums then + 7 between numbers

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```
New Contributor
Posts: 4

## Re: counting in a colums then + 7 between numbers

Hi Tom,

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

Thánks Rebekka

☑ This topic is solved.