Subscribe Bookmark RSS Feed

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

poulravn

Community Trekker

Joined:

Jan 25, 2012

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

Community Trekker

Joined:

Sep 16, 2012

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

Community Trekker

Joined:

Jan 25, 2012

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

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

poulravn

Community Trekker

Joined:

Jan 25, 2012

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

Community Trekker

Joined:

Jul 25, 2014

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