Data in the same rows

Reply
Occasional Contributor
Posts: 18

Data in the same rows

Hi All,

I am wondering how can i split the below data into the expected format, i tried with different DO statements but not getting the expected results.

The input have more than 1000 records, and each Team leader name presents at the end of team listing as highlighted, column ID is unique.

Please advise.

Input data format:

idNameSales
14star warriors20
112Legends25
8Mike's Team45
80cashcow10
67Dolphin25
54Bullets45
34Rock Stars10
1John's Team90
124Pigeons23
44Shark10
129Tigers10
52Lions20
40Bob's Team63

Output format:

idNameSalesTeam leader
14star warriors20Mike Team
112Legends25Mike Team
8Mike Team45
80cashcow10John Team
67Dolphin25John Team
54Bullets45John Team
34Rock Stars10John Team
1John Team90
124Team AA23Bob Team
44Shark10Bob Team
129Tigers10Bob Team
52Lions20Bob Team
Super User
Posts: 5,256

Re: Data in the same rows

First, you need a rule how to identify teams, and looking at this data, it seems to look at if Name contains the word 'Team'.

I am curious about the sort order, how did this happen, is it done manually?

I would first add a current sort order id to the data (sortorder = _n_).

The resort the data in reverse so that Teams will in front of their members,

Then you could create your TeamLeader variable using RETAIN and conditional processing on Name.

Data never sleeps
Occasional Contributor
Posts: 18

Re: Data in the same rows

Actually, this data is pull from third party system, the word "Team"  i just used for understanding purpose.

the data does not have the word "Team", it should have JOHN, BOB, MIKE alone.

Also, the team list is not static. For EX : this month John can have 4 teams, but the next month the new team can be added or existing team can be deleted under him.

Super User
Posts: 5,256

Re: Data in the same rows

So, how would you know what a tem (leader is). Seems like you need require some changes to that report, if you wish to do something that works over time.

Data never sleeps
SAS Super FREQ
Posts: 683

Re: Data in the same rows

Hi angorwat

Have a look at the code below, it will identify the teams it also gives each team an id, with this data you should be able to build what you need. The rule to identify the team is, that the sum of all previous sales are equal to the team sales.

data have;
  infile cards dlm=",";
 
input
    id :
8.
    name :
$32.
    sales :
8.
  ;
  cards;
14,star warriors,20
112,Legends,25
8,Mike's Team,45
80,cashcow,10
67,Dolphin,25
54,Bullets,45
34,Rock Stars,10
1,John's Team,90
124,Pigeons,23
44,Shark,10
129,Tigers,10
52,Lions,20
40,Bob's Team,63
;

data want;
  set have;

 
*
  * accumulate the sales numbers
  *;

  _saleSum + sales;

 
*
  * check if we are on the teams line
  * and set an indicator
  *;

 
if (_saleSum - sales) = sales then do;
    team =
1;
 
end;

 
output;

 
*
  * if we are on a team line
  * reset the _saleSum and increment team id
  *;

 
if team = 1 then do;
    _saleSum =
0;
    teamID +
1;
 
end;
run;
Super User
Posts: 5,256

Re: Data in the same rows

Neat, but what happens for some reason if there are only a team leader, no other...?

Or there are id with same sales within a team?

Still urge Angorwat to get some more consistent data.

Data never sleeps
Occasional Contributor
Posts: 18

Re: Data in the same rows

Thanks Bruno and LinusH for your help.

It really give me an better idea to approach this issue, as the data is more messy than what I put in the example.

Getting closer to my answer now.

Ask a Question
Discussion stats
  • 6 replies
  • 249 views
  • 4 likes
  • 3 in conversation