counting in a colums then + 7 between numbers

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

 

Thank you so much and please ask if you need more information Smiley Happy

 

Rebekka


Accepted Solutions
Solution
‎03-16-2018 04:23 PM
Super User
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

View solution in original post


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

 

Thank you so much and please ask if you need more information Smiley Happy

 

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 123 views
  • 2 likes
  • 5 in conversation