turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Hast table codes question

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-10-2016 12:01 AM - edited 02-11-2016 06:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-10-2016 06:29 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-10-2016 06:54 AM - edited 02-10-2016 06:57 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-10-2016 07:42 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-11-2016 06:58 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-14-2016 08:15 AM

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

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