## Count formula?

Contributor

Joined:

Nov 17, 2016

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.

Thanks

7 REPLIES

Community Trekker

Joined:

Feb 16, 2016

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?

Contributor

Joined:

Nov 17, 2016

I don't need to the actual counts, but a new variable that I can use to sort and group by. For example, in another data set I was able to create a new variable that counted the number of rows and gave each row a number, per subject. That was possible sine every subject had 24 rows of data. In my current data set, some subjects have 1 row while other have 5 or 38 or 10, etc.

so...
Subj visit code Counts_I_Want
001 1 7 1 1
001 1 5 2 2
001 2 3 3 1
001 3 7 4 1
001 3 6 5 2
002 1 4 1 1
003 1 7 1 1
003 2 5 2 1
003 2 0 3 2

Joined:

Apr 3, 2013

You want something like this:

If( Row() == 1, 1,

If( :Subj[Row()] == :Subj[Row() - 1], :Count Formula[Row() - 1] + 1,

1)

)

Note that this assumes the data is sorted by the SUBJ column.

Dan Obermiller

Community Trekker

Joined:

Feb 16, 2016

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.

Super User

Joined:

Jun 22, 2012

The formula you want to use is:

col number(:Subj,:Visit)

col number(:Subj,:Subj,:Visit);

Each row's value for Counts_I_Want will have the count of the number of rows for that subject for the visit

Jim

Staff

Joined:

Apr 26, 2012

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."

Super User

Joined:

Jun 23, 2011

try this one:

index