Hast table codes question

Reply
Regular Contributor
Posts: 152

Hast table codes question

[ Edited ]

I have a SAS table like below:

 

 

Number      Date  

1                 10

1                  15

1                   21

1                  30

1                  50

2                  5

2                  7

2                  8

3                   3

3                  6

many records ....

.....

....    

 

I want to do a rolling count by comparing all the previous rows  with each row within the same number by the date. if the date differennce is less than 7 (for exmaple) then it will be counted as 1 and I want to sum up the total count for each row

 

 the output table like(assume the date difference is 7):

Number      Date            count

1                 10                 1(there is no previous record for number 1 so the count =1(only 10))

1                  15                 2(15-10=5 less than 7 so the count is 2(15 and 10))

1                   21                2(21-15=6 less than 7 and 21-10>11 greater than 7 so the count is 2(15 and 21))

1                  30                 0

1                  50                 0

2                  5                  1(itself count as 1)

2                  7                   1(10-5=5<7 so count=2(5 and 7))

2                  8                   2(8-7=1<7 and 8-5=3<7 so count=3(8 and 7 and 5))

3                   3                  1(itself count as 1)

3                  6                  2(6-3=3<7 so count=2(3 and 6))

 

 

I want to create the output table use hasht table and not using SQL or ARRAY statement

 

 

Thanks

Super User
Super User
Posts: 7,727

Re: Hast table codes question

Why do you have to do this by hash table?  It would seem to me to be a simple process - sort the data, retain count, if then logic.  Something like:

proc sort data=have;
  by number date;
run;
 
data want;
  set have;
  retain count;
  by number;
  if first.number then count=0;
  else do;
    if date - lag(date) <= 7 then count=count+1;
  end;
run;
Regular Contributor
Posts: 152

Re: Hast table codes question

[ Edited ]

Hi RW9, it is not that simple. I want the rolling statistics (counts) for each row within same number. your code can only calculate the count between the current row and previous row but I want the count for all the previous rows for the current row (rolling counts).

 

For example, for number 1, if the current date is 10 and previous date is 5 and previous(2) is 1, then the count for current date is 1 since 10-5=5<7(included for count) and 10-1=9>7(excluded for count)

 

THanks

Super User
Super User
Posts: 7,727

Re: Hast table codes question

Not sure the test example you have given me is correct then, row 3 should be 2 yes?  I am not sure I follow the logic here either.  If previous record is within 7 days then that can only mean the date prior to that is >= 7 no?

Regular Contributor
Posts: 152

Re: Hast table codes question

Hi RW9, I have modified my data. row 3 is 2 now.

so bascally each row will be compared with all the previous rows for that number.

THX!

Super User
Posts: 5,391

Re: Hast table codes question

"...use hasht table and not using SQL or ARRAY statement". Is this homework? If so it's something we appreciate that you tell us.

And like @RW9, I don't really get the logic. If you tell us a bit more about the underlying requirement it may help us understand.
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 335 views
  • 0 likes
  • 3 in conversation