My data are organized such that an individual subject can have multiple lines of data, and each subject can have multiple dates/visits, and multiple lines of codes within each date/visit. However, the number of lines of data is different for each subject. I'm trying to count up the number of lines of data per subject, per date/visit, and lines of code within each date/visit. So, line 1=1, line 2=2, etc. All the formulas seem to require that I set a start and end number for a count, but that's not possible since the end varies.
Can you tell me which formula I should be using and provide some advice on setting it up? Apparently I'm missing this in my interpretation.
It sounds like you can get this through a table summary:
If you go to Tables-->Summary then select the columns you want to group by (e.g. subject, visit date). This will give you the number of rows that satisfy those conditions.
Below gives number of students by age and gender:
dt = Open("$SAMPLE_DATA/Big Class.jmp")<< Summary(Group( :age, :sex ));
Is that what you mean?
You want something like this:
If( Row() == 1, 1,
If( :Subj[Row()] == :Subj[Row() - 1], :Count Formula[Row() - 1] + 1,
Note that this assumes the data is sorted by the SUBJ column.
I see, then Dan's code should work. You can also use the lag function, so for you example data Subject Counts could be:
If(Lag(:Subj, 1) != :Subj | Is Missing(Lag(:Subj, 1)), 1, Lag(:Subj Counts, 1) + 1)
and visit counts could be:
If(Lag(:Subj, 1) != :Subj | Is Missing(Lag(:Subj, 1)) | Lag(:visit, 1) != :visit, 1, Lag(:Visit Counts, 1) + 1)
This still relies on sorting by subj and visit though.
The formula you want to use is:
Each row's value for Counts_I_Want will have the count of the number of rows for that subject for the visit
Col Number( :age, :age )
Col Number is a formula function in the Statistical group of function
It counts the number of things in a column
col number(:age) would ruturn the number of rows
col number(:age,:age) returns the number of times each Age occurs in the column for each row
if "14" shows up 10 times in the column, then for each row with a 14 in it, the formula returns "10."