Programming the statistical procedures from SAS

SAS Return date 'number/order' per user

Reply
Occasional Contributor
Posts: 12

SAS Return date 'number/order' per user

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.

Super Contributor
Posts: 578

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

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

                                                                FirstTotal
                                             Obsusername Date Days

                                              1 player1 17840 
                                              2 player2 17843 
Occasional Contributor
Posts: 12

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

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

Super User
Posts: 18,569

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

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.

Ask a Question
Discussion stats
  • 3 replies
  • 185 views
  • 0 likes
  • 3 in conversation