Combining data by ID

[Julie]

If I have an Excel file that reads like below (partial file):

ID A B C D

030 2 9 5 5
030.1 4 8 9 4
030.2 6 7 9 3
030.5 9 3 5 3
020 8 7 3 2
011 2 4 3 9
011.1 2 4 2 7
011.2 9 3 4 6
012 4 5 2 9
012.1 6 7 3 6
013 4 9 2 3

Is there a quick way (either in Excel or SAS) to sum the data lines
with "similar" IDs (in this case 030, 030.1, 030.2, and 030.5 are
"similar" to each other. 012 and 012.1 are "similar" to each other,
etc.) So for ID "011", for example, I'd need to end up with a data
line (either within Excel or SAS)that reads 011 13 11 9 22 since the
numbers on the lines with ID 011, 011.1, and 011.2 would have to be
summed. In the final dataset, I would need summed data for each ID. I
hope you can understand what I'm trying to do... (in some ways this is
similar to the SAS question I posted a few days ago). I had been doing
this manually line-by-line with the sum() function... I am wondering
if there is a quicker way to do this?? Appreciate any help!!

Julie

 

[Debra Dalgleish]

You could create a pivot table from the data, with ID in the row area,
and A, B, C and D in the data area.

There are instructions and links on Jon Peltier site:

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

Then, right-click on the ID field button.
Choose Group and Show Detail> Group
Group by 1, click OK