Create a column that contains a count from 1 to the number of rows

[#1]

Hi,

here is another question:

I sometimes want to create graphs where the x-axis is a count (as the row number), instead of column from the data table itself, for example time. Much like when doing a graph in Excel using the 'line' graph option. To do that I need an easy way to create a column which simple is the count or number of the row.

In Base SAS it would be:

data y;set x;nobs=_N_;run;

which adds nobs as a count of the observation number.

Thanks in advance

Poul

[#2]

Hi.

In what other way would you like to create the nobs?

You say in Base SAS it would be...so on;

So, you need an alternative to what you have already provided as solution?

Thanks!

I see this is posted in JMP, ok, so have you tried the COUNT function?

[#3]

True, this was posted in JMP discussion, - I did not mention. However, I looked into the Count as you suggested, and fail to see, how it could be used as desired. What does count do, actually? It seems that would have to count to an upper value, which is the number of rows, but how to obtain that?

Sorry, for asking these simple questions here. After starting to use JMP I have to learn many things over. In Base SAS it was very simple.

Regards

Poul

[#4 · Accepted solution]

There are at least three quick ways to do this.

1. In a new empty column there is the option "initialize data" in the column dialog. Choose sequence, default should be a linear series in steps of 1.

2. A single-command column formula: Row()

3. Add two numbers (e.g. 1 and 2) in the first two rows of your counter column and right click and select the adequate Fill option, e.g Continue sequence to end of table....

[#5]

All of the recommended work. Thanks.

That makes me aks the next question:

I used to do like this in SAS code:

data mydata(drop=column_ID_1_old);

retain column_ID_1;

column_ID_1_old=column_ID_1;

set mydata;

if nobs=. then nobs=1;

else if (column_ID_1=column_ID_1_old) then nobs=nobs+1;else nobs=1;

run;

column_ID_1 would have a categorical value of some kind, based upon which the numbering would restart, each time the column_ID_1 changed its value. This is very useful when creating graphs that have panes that wrap. All of these must have the same x-axes, otherwise the graphs do not line up nicely. At least so I found.

So, my next question is: How to do this in JMP? Create the new column so that it restarts counting when another columns changes its value.

Regards Poul

[#6]

This is a great question, PoulRavn, I need to know how to do this as well.

[#7]

I'm not getting the option 3 on my end (Using JMP 16). When I do the right click after selection first two rows- it only provides me options for fill as "to the end of the table" or "fill to row(x)". 

What am I missing here?

[#8]

Never mind- I figured it out. My column was set a Character.