Create a variable with count of not missong obs

[foxrol94]

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

[Reeza]

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

---

[FreelanceReinh]

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;
  h.add();
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();.

[Kurt_Bremser · Accepted solution]

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;