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.
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 |
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.