topic Re: SAS Return date 'number/order' per user in Statistical Procedures

SAS Return date 'number/order' per user

tobriain — Wed, 05 Jun 2013 19:42:11 GMT

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.

Re: SAS Return date 'number/order' per user

DBailey — Wed, 05 Jun 2013 21:58:13 GMT

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

Re: SAS Return date 'number/order' per user

tobriain — Wed, 05 Jun 2013 22:35:00 GMT

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

Re: SAS Return date 'number/order' per user

Reeza — Wed, 05 Jun 2013 23:58:28 GMT

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.