topic Re: how to add a sequence number in SAS Programming

how to add a sequence number

amygoodman — Sat, 14 Jan 2017 04:48:51 GMT

variable A variable B

1 5

2 7

2 6

i want assign a sequence number to the above, when variable A =1 and variable B=5 then seq=1

when variable A=2 and Variable B=7 then seq=1, when variable A=2 and variable B=6 then seq=2

basically, i want to have a sequence within variable A, only when variable B change, then increase seq by order

How to add sequence to repeat numbers

amygoodman — Sat, 14 Jan 2017 04:45:43 GMT

variable A variable B

1 5

2 7

2 6

i want assign a sequence number to the above, when variable A =1 and variable B=5 then seq=1

when variable A=2 and Variable B=7 then seq=1, when variable A=2 and variable B=6 then seq=2

basically, i want to have a sequence within variable A, only when variable B change, then increase seq by order

Re: how to add a sequence number

LaurieF — Sat, 14 Jan 2017 05:37:10 GMT

The only tricky thing in your data is that it is inherently unsorted. But I believe by-group processing is your answer. Here's how I did it:

data source;
infile cards;
input a
      b;
cards;
1                                 5
1                                 5
1                                 5
1                                 5
1                                 5
2                                 7
2                                 7
2                                 7
2                                 7
2                                 6
2                                 6
2                                 6
;
run;

data target;
set source;
by a b notsorted;
retain sequence;
select;
   when(first.a)
      sequence = 1;
   when(first.b) 
      sequence + 1;
   otherwise;
   end;
run;

Note the notsorted keyword on the by statement - because in a's second group 7 is higher than 6, the by processing would complain; notsorted suppresses that.

Re: how to add a sequence number

art297 — Sat, 14 Jan 2017 05:44:15 GMT

Similar to the code that Laurie proposed, but a little simpler (I think).

data have;
input a b;
cards;
1 5
1 5
1 5
1 5
1 5
2 7
2 7
2 7
2 7
2 6
2 6
2 6
;

data want;
set have;
by a b notsorted;
if first.a then sequence = 1;
else if first.b then sequence + 1;
run;

Art, CEO, AnalystFinder.com

Re: how to add a sequence number

amygoodman — Sun, 15 Jan 2017 03:16:45 GMT

works great! thanks a lot !

Re: how to add a sequence number

amygoodman — Sun, 15 Jan 2017 03:17:45 GMT

thanks! works great