cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
poulravn
Level IV

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

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

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

View solution in original post

7 REPLIES 7
ancatilea
Level I

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

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?

poulravn
Level IV

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

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

ms
Super User (Alumni) ms
Super User (Alumni)

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

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

poulravn
Level IV

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

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

dwaterson
Level III

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

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

lmrsh1001
Level I

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

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?

lmrsh1001
Level I

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

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