DATA Step, Macro, Functions and more

Group the data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Group the data

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

 

 

 


Accepted Solutions
Solution
2 weeks ago
Contributor
Posts: 49

Re: Group the data

Posted in reply to zhaoxuan210
 
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;

View solution in original post


All Replies
Solution
2 weeks ago
Contributor
Posts: 49

Re: Group the data

Posted in reply to zhaoxuan210
 
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;

Occasional Contributor
Posts: 7

Re: Group the data

Posted in reply to soham_sas
Thank you! Why we still need if last.partnumber in the end?
PROC Star
Posts: 1,836

Re: Group the data

Posted in reply to zhaoxuan210
 
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;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 116 views
  • 1 like
  • 3 in conversation