topic Re: number rows from 1 to 3 only on criteria in SAS Programming

number rows from 1 to 3 only on criteria

Nasser_DRMCP — Thu, 09 Sep 2021 08:56:25 GMT

Hello,

in this dataset "have". i would like to get a flag populated only when i_jour_sem = i_sem.

the first time that this criteria is met then the flag shoul be 1, the second time then 2, the 3è then 3, but the 4 times the 1, the 5 times then 2 ...

so , only whenever i_jour_sem = i_sem.the flag should be 1,2,3,1,2,3...

thanks for your help in advance

Data have  ;
infile datalines ;
input name $ week i_jour_sem i_sem ;
datalines ;
Andrew 39 1 1
Andrew 39 2 1
Andrew 39 3 1
Andrew 39 4 1
Andrew 39 5 1
Andrew 40 1 2
Andrew 40 2 2
Andrew 40 3 2
Andrew 40 4 2
Andrew 40 5 2
Andrew 41 1 3
Andrew 41 2 3
Andrew 41 3 3
Andrew 41 4 3
Andrew 41 5 3
Andrew 42 1 4
Andrew 42 2 4
Andrew 42 3 4
Andrew 42 4 4
Andrew 42 5 4
Andrew 43 1 5
Andrew 43 2 5
Andrew 43 3 5
Andrew 43 4 5
Andrew 43 5 5
Andrew 44 1 6
Andrew 44 2 6
Andrew 44 3 6
Andrew 44 4 6
Andrew 44 5 6
;
run ;

Re: number rows from 1 to 3 only on criteria

PeterClemmensen — Thu, 09 Sep 2021 09:02:48 GMT

What about when the values are unequal? Should the flag be missing?

Re: number rows from 1 to 3 only on criteria

Nasser_DRMCP — Thu, 09 Sep 2021 09:17:32 GMT

yes.

Re: number rows from 1 to 3 only on criteria

PeterClemmensen — Thu, 09 Sep 2021 10:09:03 GMT

Try this

data want(drop = c);
   set have;
   if i_jour_sem ne i_sem then flag = .;

   else do;
      flag = sum(c, 1);
      c = sum(c, 1);
   end;
   
   if c = 3 then c = 0;
   retain c;
run;

Re: number rows from 1 to 3 only on criteria

Nasser_DRMCP — Thu, 09 Sep 2021 11:46:15 GMT

Hello Peter,

how to do that with many names ? look at "have2" dataset.

the flag should start with 1 when a new group name starts.

many thanks

Data have2  ;
infile datalines ;
input name $ week i_jour_sem i_sem ;
datalines ;
Andrew 39 1 1
Andrew 39 2 1
Andrew 39 3 1
Andrew 39 4 1
Andrew 39 5 1
Andrew 40 1 2
Andrew 40 2 2
Andrew 40 3 2
Andrew 40 4 2
Andrew 40 5 2
Andrew 41 1 3
Andrew 41 2 3
Andrew 41 3 3
Andrew 41 4 3
Andrew 41 5 3
Andrew 42 1 4
Andrew 42 2 4
Andrew 42 3 4
Andrew 42 4 4
Andrew 42 5 4
Andrew 43 1 5
Andrew 43 2 5
Andrew 43 3 5
Andrew 43 4 5
Andrew 43 5 5
Andrew 44 1 6
Andrew 44 2 6
Andrew 44 3 6
Andrew 44 4 6
Andrew 44 5 6
Dalila 39 1 1
Dalila 39 2 1
Dalila 39 3 1
Dalila 39 4 1
Dalila 39 5 1
Dalila 40 1 2
Dalila 40 2 2
Dalila 40 3 2
Dalila 40 4 2
Dalila 40 5 2
Dalila 41 1 3
Dalila 41 2 3
Dalila 41 3 3
Dalila 41 4 3
Dalila 41 5 3
Dalila 42 1 4
Dalila 42 2 4
Dalila 42 3 4
Dalila 42 4 4
Dalila 42 5 4
Dalila 43 1 5
Dalila 43 2 5
Dalila 43 3 5
Dalila 43 4 5
Dalila 43 5 5
Dalila 44 1 6
Dalila 44 2 6
Dalila 44 3 6
Dalila 44 4 6
;
run ;

Re: number rows from 1 to 3 only on criteria

Nasser_DRMCP — Thu, 09 Sep 2021 11:50:10 GMT

hello,

sorry for my question, I found this

data want(drop = c);

set have;

by name ;

if first.name then c=0 ;

if i_jour_sem ne i_sem then flag = .;

else do;

flag = sum(c, 1);

c = sum(c, 1);

end;

if c = 3 then c = 0;

retain c;

run;