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
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
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
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.
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
works great! thanks a lot !
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.