Adding rows with some group specifications.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Adding rows with some group specifications.

Hi all,

I would like to do the following scenario.

ID      Group     Amount

1          A            30

1          B            40

1          C            25

1          D            10

2          A            20

2          B            35

2          D            10

Then I would like to call a group A and B as "first" and C and D as "second". Then I would like to add the amount within the specified group. The the result should be,

ID      Group         Amount

1         first          70 {30+40}

1        second      35 {25+10}

2         first          55 {20+35}

2        second      10 {10}

Please let me know how I could write a code to do this. Thanks! Smiley Happy


Accepted Solutions
Solution
‎05-18-2012 11:50 PM
Respected Advisor
Posts: 4,640

Re: Adding rows with some group specifications.

Assuming you don't actually want the formula within braces in the output... Use a FORMAT :

data have;
input ID group $ Amount;
datalines;
1          A            30
1          B            40
1          C            25
1          D            10
2          A            20
2          B            35
2          D            10
;

proc format;
value $ grp
"A", "B" = "First"
"C", "D" = "Second"
OTHER = "Other";
run;

proc sql;
select ID, put(group, $grp.) as group, sum(Amount) as Amount
from have
group by ID, calculated group;

quit;

PG

PG

View solution in original post


All Replies
Solution
‎05-18-2012 11:50 PM
Respected Advisor
Posts: 4,640

Re: Adding rows with some group specifications.

Assuming you don't actually want the formula within braces in the output... Use a FORMAT :

data have;
input ID group $ Amount;
datalines;
1          A            30
1          B            40
1          C            25
1          D            10
2          A            20
2          B            35
2          D            10
;

proc format;
value $ grp
"A", "B" = "First"
"C", "D" = "Second"
OTHER = "Other";
run;

proc sql;
select ID, put(group, $grp.) as group, sum(Amount) as Amount
from have
group by ID, calculated group;

quit;

PG

PG
Occasional Contributor
Posts: 12

Re: Adding rows with some group specifications.

Thank you very much! That works!

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 217 views
  • 0 likes
  • 2 in conversation