topic Re: Group the data in SAS Programming

Group the data

zhaoxuan210 — Thu, 05 Jul 2018 20:40:19 GMT

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 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

Re: Group the data

soham_sas — Fri, 06 Jul 2018 11:45:42 GMT

 
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;

Re: Group the data

zhaoxuan210 — Fri, 06 Jul 2018 17:44:45 GMT

Thank you! Why we still need if last.partnumber in the end?

Re: Group the data

novinosrin — Sat, 07 Jul 2018 18:03:32 GMT

 
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;