topic Re: counting in a colums then + 7 between numbers in SAS Programming

counting in a colums then + 7 between numbers

Rebekka — Fri, 16 Mar 2018 14:17:00 GMT

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

Re: counting in a colums then + 7 between numbers

Reeza — Fri, 16 Mar 2018 14:38:00 GMT

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

Re: counting in a colums then + 7 between numbers

Ksharp — Fri, 16 Mar 2018 15:20:56 GMT

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;

Re: counting in a colums then + 7 between numbers

SuryaKiran — Fri, 16 Mar 2018 17:34:43 GMT

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;

Re: counting in a colums then + 7 between numbers

Tom — Fri, 16 Mar 2018 18:46:56 GMT

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

Re: counting in a colums then + 7 between numbers

Rebekka — Fri, 16 Mar 2018 20:17:17 GMT

Hi Tom,

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

Thánks Rebekka