topic Re: Create a variable with count of not missong obs in SAS Programming

Create a variable with count of not missong obs

foxrol94 — Mon, 20 Sep 2021 15:54:06 GMT

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

Re: Create a variable with count of not missong obs

Reeza — Mon, 20 Sep 2021 16:13:50 GMT

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

Re: Create a variable with count of not missong obs

FreelanceReinh — Mon, 20 Sep 2021 17:43:20 GMT

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

Re: Create a variable with count of not missong obs

Kurt_Bremser — Tue, 21 Sep 2021 09:47:04 GMT

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;