SAS Return date 'number/order' per user

[tobriain]

I'm running the following query for a number of users, and

proc SQL number; 
select monotonic() as numberOfDaysActive, 
username as username,
datepart(date) Format=ddmmyy8. AS date 
from work.temp; quit; 

Monotonic() doesn't give the right value for numberOfDaysActive, as it just counts from the top of the rows returned regardless of user. For each user, I need to know when it is the users first day in the database, and a day count after that for each day the user is present. For example, I want to be able to run calculations on all users 'first day out'. I would also like to be able to calculate the average spend on day 1, average spend on day 2 etc.

Sample data here, as follows:

DATA sample;

   INPUT username $ amount date : ddmmyy8.;

   FORMAT date worddatx20.;

   DATALINES;

player1 100 04/11/08

player2 120 07/11/08

player1 50 05/11/08

player2 10 09/11/08

player2 35 15/11/08

;

PROC PRINT; RUN;

Would appreciate any help at all.

Thanks in advance.

[DBailey]

So this code

proc sql;

create table want as

select

    username,

    min(date) as FirstDate,

    count(*) as TotalDays

from

    sample

group by username;

quit;

produces this result:

16:56 Wednesday, June 5, 2013   1

	First	Total
	Obs	username	Date	Days

	1	player1	17840	2
	2	player2	17843	3

[tobriain]

That gives a count of the total number of rows. There are multiple entries per day.

INPUT username $ amount date5 : ddmmyy8.;
DATALINES;
player1 90 12/11/08
player1 100 04/11/08
player2 120 07/11/08
player1 50 05/11/08
player1 30 05/11/08
player1 20 05/11/08
player2 10 09/11/08
player2 35 15/11/08
PROC PRINT; RUN;

I want to see this:

player1 90 12/11/08 3
player1 100 04/11/08 1
player2 120 07/11/08 1
player1 50 05/11/08 2
player1 30 05/11/08 2
player1 20 05/11/08 2
player2 10 09/11/08 1
player2 35 15/11/08 1

[Reeza]

Can you use a data step instead of proc sql?

Sort the data first and then use first. and last. to assign your counts. SQL doesn't work well for enumerating by groups.