I want to regroup the data, can anyone help me?
Orginal: (PartNumber and Line Number are sorted)
PartNumber LineNumber
A 01
A 02
A 03
A 01
A 02
A 01
A 02
A 03
A 04
A 05
B 01
B 01
B 02
B 01
Target:
PartNumber LineNumber Group
A 01 01
A 02 01
A 03 01
A 01 02
A 02 02
A 01 03
A 02 03
A 03 03
A 04 03
A 05 03
B 01 01
B 01 02
B 02 02
B 01 03
I want every time when Line number = 1 , the group number +1, every time the part number change , the group number back to 1
data have;
infile cards dlm='09'x;
input PartNumber$ LineNumber;
cards;
A 01
A 02
A 03
A 01
A 02
A 01
A 02
A 03
A 04
A 05
B 01
B 01
B 02
B 01
;
run;
data want;
set have;
by PartNumber;
retain group;
if first.PartNumber and linenumber=1 then group=1;
else if linenumber=1 then group+1;
output;
if last.partnumber;
run;
data have;
infile cards dlm='09'x;
input PartNumber$ LineNumber;
cards;
A 01
A 02
A 03
A 01
A 02
A 01
A 02
A 03
A 04
A 05
B 01
B 01
B 02
B 01
;
run;
data want;
set have;
by PartNumber;
retain group;
if first.PartNumber and linenumber=1 then group=1;
else if linenumber=1 then group+1;
output;
if last.partnumber;
run;
data have;
infile cards ;
input PartNumber$ LineNumber ;
cards;
A 01
A 02
A 03
A 01
A 02
A 01
A 02
A 03
A 04
A 05
B 01
B 01
B 02
B 01
;
run;
data want;
set have;
by partnumber ;
retain group;
if first.partnumber then group=linenumber;
else if linenumber=1 then group+1;
run;
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.