BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted

How to define a Count formula within a Subject

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Count formula?

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
7 REPLIES 7

Re: Count formula?

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?

Re: Count formula?

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

Re: Count formula?

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

Re: Count formula?

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.

Re: Count formula?

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

Re: Count formula?

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

JMP Systems Engineer, Pharm and BioPharm Sciences

try this one:

index