topic Re: Sequnce number in SAS Programming

Sequnce number

deleted_user — Thu, 13 Jan 2011 17:34:25 GMT

Hi!

I have a table to which I want to add a sequence no to uniquely identify records.
What would be the best way to do this ?I have read about _n_ but not sure hot to use it.
Any help would be appreciated.

Thanks!

Re: Sequnce number

chang_y_chung_hotmail_com — Thu, 13 Jan 2011 17:51:20 GMT

_n_ is not that hot... 🙂

/* add obs number using _n_ */

data class;

length myObs 8;

set sashelp.class;

myObs = _n_;

run;

/* check */

proc print data=class(obs=5);

run;

/* on lst

Obs Obs Name Sex Age Height Weight

1 1 Alfred M 14 69.0 112.5

2 2 Alice F 13 56.5 84.0

3 3 Barbara F 13 65.3 98.0

4 4 Carol F 14 62.8 102.5

5 5 Henry M 14 63.5 102.5

Re: Sequnce number

MikeZdeb — Fri, 14 Jan 2011 03:18:42 GMT

hi ... you have to be careful using _n_ since it counts passes through the data step, it does not count observations, for example ...

data males;
set sashelp.class;
if sex eq 'M';
n1 = _n_;
n2+1;
drop sex;
run;

proc print data=males;
var name n1 n2;
run;

Obs Name n1 n2
1 Alfred 1 1
2 Henry 5 2
3 James 6 3
4 Jeffrey 9 4
5 John 10 5
6 Philip 15 6
7 Robert 16 7
8 Ronald 17 8
9 Thomas 18 9
10 William 19 10

and, to learn a bit more (the difference between WHERE and a subsetting IF) ...

data males;
set sashelp.class;
where sex eq 'M';
n1 = _n_;
n2+1;
drop sex;
run;

proc print data=males;
var name n1 n2;
run;

Obs Name n1 n2
1 Alfred 1 1
2 Henry 2 2
3 James 3 3
4 Jeffrey 4 4
5 John 5 5
6 Philip 6 6
7 Robert 7 7
8 Ronald 8 8
9 Thomas 9 9
10 William 10 10

so, I'd use the construct ... var + 1;

to create a sequence number since is works with both IF and WHERE

Re: Sequnce number

Ksharp — Fri, 14 Jan 2011 07:58:00 GMT

Hi. Mike
Can you explain why it will be appear?
It is very interesting.

Ksharp

Re: Sequnce number

MikeZdeb — Fri, 14 Jan 2011 16:39:50 GMT

hi ... not sure what part of the SAS code the question is about

if it's about how to add a sequence number, it's a good way to learn about a lot of
stuff that goes on in a data step

here's a data step that tries to create 7 sequence numbers
if you understand the output, you know (learn ?) a lot

data males;
retain n4 0 n5 n7;
set sashelp.class;
if sex eq 'M';
n1=_n_;
n2+1;
n3=n3+1;
n4=n4+1;
n5=n5+1;
n6=sum(n6,1);
n7=sum(n7,1);
keep n: ;
run;

proc print data=males;
var name n1-n7;
run;

Obs Name n1 n2 n3 n4 n5 n6 n7

1 Alfred 1 1 . 1 . 1 1
2 Henry 5 2 . 2 . 1 2
3 James 6 3 . 3 . 1 3
4 Jeffrey 9 4 . 4 . 1 4
5 John 10 5 . 5 . 1 5
6 Philip 15 6 . 6 . 1 6
7 Robert 16 7 . 7 . 1 7
8 Ronald 17 8 . 8 . 1 8
9 Thomas 18 9 . 9 . 1 9
10 William 19 10 . 10 . 1 10

n1 / that is based on _n_ , an automatic SAS variable that counts passes through
the data step and there are 19 passes since there are 19 observations in data
set SASHELP.CLASS ... one feature, these are the original observation numbers
from data set SASHELP.CLASS

n2 / there are a lot of features here ... this construct (var + 1) implies two things: first, the
variable is automatically retained (not set to missing each pass back to the top
of the data step); second, the initial value of var is 0 ... so this is like have the statement

retain n2 0;

in the data step ... but you do not have to write that statement

n3 / does not work since the initial value of n3 when that statement is first executed is
MISSING and adding anything to a missing value gives a missing result

n4 / that works fine: the initial value of n4 is set to 0 in the retain statement;
the value of n4 is retained and not set to missing each pass back to the top of the data step

so n4 is the same as n2 ... but using n2+1 instead of n4=n4+1 means that you do not
need the retain statement for n2

n5 / does not work ... the value of n5 is retained, but the initial value of n5 is missing and
if you add anything to a missing value, the result is missing

n6 / does not work ... notice that the result is always 1 since functions (SUM) ignore missing
values, so when you add 1 to a missing value you get 1 ... but n6 is not retained so it
always gets set back to missing at the top of the data step

n7/ that works since it uses the SUM function like n6, but the value is retained

but ... if your question is about why _n_ works with WHERE and not with IF, just look at the LOG
after you run both jobs (one with IF and one with WHERE)

the one with IF makes 19 passes through the data step since every observation in SASHELP.CLASS is processed by the SET statement ... the one with WHERE makes 10 passes through the data step since the WHERE statement can be thought of as "peeking" at your data set to see if it actually has to process an observation in the data set ... if the WHERE statement is FALSE, the observation is never "seen" by the SET statement

hope all that makes sense

Re: Sequnce number

deleted_user — Fri, 14 Jan 2011 18:38:39 GMT

I do not have a IF/WHERE but I was using _n_ as below to generate the sequence number.
data Class;
length SEQ_NO 8;
set Class;
SEQ_NO = _n_;
run;

Do you suggest that I use this instead?
length SEQ_NO 8;
set Class;
n1 = _n_;
SEQ_NO = n2+1;
run;

Thanks!

Re: Sequnce number

MikeZdeb — Fri, 14 Jan 2011 19:09:45 GMT

hi ... no, my suggestion is ...

data class;
set sashelp.class;
SEQ_NO +1 ;
run;

and .. there's no reason for this ...

length SEQ_NO 8;

the default length of numeric variables in SAS is 8 bytes

Re: Sequnce number

deleted_user — Fri, 14 Jan 2011 19:41:16 GMT

I get 2 errors. How do I fix them?
ERROR: File SASHELP.Class.DATA does not exist
ERROR: Variable SEQ_NO not found.

I am trying to add a sequnce number column to the existing dataset CLASS.

Re: Sequnce number

MikeZdeb — Fri, 14 Jan 2011 19:51:36 GMT

hi ... you should post either the EXACT SAS code you used or all of the LOG file (code + messages)

otherwise it is difficult to determine why you get error messages

but, one thing you can try is ....

proc datasets lib=sashelp memtype=data;
quit;

just to see if you have access to the SASHELP library ... the above
code should list in the LOG all the datasets in the SASHELP library

Re: Sequnce number

deleted_user — Fri, 14 Jan 2011 20:49:10 GMT

I will try the access to SASHELP part, but do you think this code might produce incorrect sequnce numbers? Because I did run it 5 times and it did not give me an error.
data Class;
length SEQ_NO 8;
set Class;
SEQ_NO = _n_;
run;

thnaks!

Re: Sequnce number

MikeZdeb — Fri, 14 Jan 2011 21:50:29 GMT

hi ... yes that works but you are missing the point(s)

#1/ _n_ works in that situation, but it does not always work since
it counts passes through the data step, not observations added
to the data set created in the data step

in your SAS job, _n_ works since the number of passes through the
data step is the same as the number of observations added to data
set class

#2/ this ... length SEQ_NO 8; ... is not needed since
even without it the length of SEQ_NO is 8

Re: Sequnce number

Ksharp — Mon, 17 Jan 2011 04:33:25 GMT

Hi.
I have try your code.There is no secret about it.
If you have sorted the sashelp.class then you will get the output like this:
[pre]
Name Age Height Weight n1 n2
Alfred 14 69 112.5 10 1
Henry 14 63.5 102.5 11 2
James 12 57.3 83 12 3
Jeffrey 13 62.5 84 13 4
John 12 59 99.5 14 5
Philip 16 72 150 15 6
Robert 12 64.8 128 16 7
Ronald 15 67 133 17 8
Thomas 11 57.5 85 18 9
William 15 66.5 112 19 10
[/pre]
That means when the iterative number is ten (i.e. _n_ = 10) ,output an observation,this time n2+1.

Ksharp