🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

## Create a variable with count of not missong obs

Hi

I'm a bit upset about myself cause i can find the solution of my problem.

I have a dataset test1 with one variable Var1 and i need to create a second variable Var2with number 1, 2,3 etc.. except that each time i have two identical value in the first column, the value in the second column will be empty.

i tried the code below and nothing works:

``````data test2;
set test1;
by Var1;
if first.Var1 then Var2 = _N_;
else Var2 =.;
run;``````

Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Create a variable with count of not missong obs

``````data want;
set have;
by var1 notsorted;
retain v2 0;
if first.var1 ne last.var1
then do;
v2 + 1;
var2 = v2;
end;
else var2 = .;
drop v2;
run;``````
3 REPLIES 3
Super User

## Re: Create a variable with count of not missong obs

I suspect you have a note in your log about having unsorted data?

BY processing and FIRST only work if records are in order and in this case they're not in order. Does the order matter to the value assigned in VAR2 or do you just need to map then to a number?

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

@foxrol94 wrote:

Hi

I'm a bit upset about myself cause i can find the solution of my problem.

I have a dataset test1 with one variable Var1 and i need to create a second variable Var2with number 1, 2,3 etc.. except that each time i have two identical value in the first column, the value in the second column will be empty.

i tried the code below and nothing works:

``````data test2;
set test1;
by Var1;
if first.Var1 then Var2 = _N_;
else Var2 =.;
run;``````

Regards

Jade | Level 19

## Re: Create a variable with count of not missong obs

Hi @foxrol94,

So, in order to set Var2 correctly, you need to have SAS remember all Var1 values it has already seen while reading through dataset TEST1? This is a typical application of the hash object.

``````data want(drop=_c);
if _n_=1 then do;
dcl hash h();
h.definekey('Var1');
h.definedone();
end;
set test1;
if Var1 ne ' ' & h.check() then do;
_c+1;
Var2=_c;
end;
run;``````

If a new non-missing* Var1 value is encountered, the return code of the CHECK method is not zero and the DO-END block is executed: A counter _c, initialized to zero, is incremented (and automatically retained), Var2 (which otherwise would be missing) is set to the new value of _c and the value of Var1 is added to the hash object ("lookup table").

*Edit: inserted IF condition Var1 ne ' ' to exclude missing values from the count. Alternatively, you could insert an additional h.add(); after h.definedone();.

Super User

## Re: Create a variable with count of not missong obs

``````data want;
set have;
by var1 notsorted;
retain v2 0;
if first.var1 ne last.var1
then do;
v2 + 1;
var2 = v2;
end;
else var2 = .;
drop v2;
run;``````
Discussion stats
• 3 replies
• 510 views
• 1 like
• 4 in conversation