how to add a sequence number

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

how to add a sequence number

variable A                variable B

1                                 5

1                                 5

1                                 5

1                                 5

1                                 5

2                                 7

2                                 7

2                                 7

2                                 7

2                                 6

2                                 6

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


Accepted Solutions
Solution
‎01-14-2017 10:17 PM
PROC Star
Posts: 7,437

Re: how to add a sequence number

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

View solution in original post


All Replies
Super Contributor
Posts: 252

Re: how to add a sequence number

[ Edited ]

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.

New Contributor
Posts: 4

Re: how to add a sequence number

thanks! works great
Solution
‎01-14-2017 10:17 PM
PROC Star
Posts: 7,437

Re: how to add a sequence number

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

New Contributor
Posts: 4

Re: how to add a sequence number

works great! thanks a lot !

New Contributor
Posts: 4

How to add sequence to repeat numbers

variable A                variable B

1                                 5

1                                 5

1                                 5

1                                 5

1                                 5

2                                 7

2                                 7

2                                 7

2                                 7

2                                 6

2                                 6

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 418 views
  • 1 like
  • 3 in conversation