I need to write code create rows inside an existing dataset based on groupings. I have the following existing dataset:
Year | Area | Profit |
2001 | A | 1 |
2002 | A | 2 |
2001 | B | 1 |
2001 | C | 3 |
2002 | C | 1 |
2001 | E | 4 |
2002 | E | 2 |
2001 | F | 3 |
2002 | F | 4 |
I need to add in more rows corresponding to two more areas. Area D consists of A,B,C and Area G consists of E and F. I need to sum over the groups and years and add them into the existing dataset so it looks as such:
Year | Area | Profit |
2001 | A | 1 |
2002 | A | 2 |
2001 | B | 1 |
2001 | C | 3 |
2002 | C | 1 |
2001 | E | 4 |
2002 | E | 2 |
2001 | F | 3 |
2002 | F | 4 |
2001 | D | 5 |
2002 | D | 3 |
2001 | G | 7 |
2002 | G | 6 |
Try MultiLabel Formats with PROC MEANS.
data have;
input Year Area $ Profit;
cards;
2001 A 1
2002 A 2
2001 B 1
2001 C 3
2002 C 1
2001 E 4
2002 E 2
2001 F 3
2002 F 4
;;;;
run;
proc format;
value $ area_fmt (multilabel)
'A' = 'A'
'B' = 'B'
'C' = 'C'
'A', 'B', 'C' = 'D'
'E' = 'E'
'F' = 'F'
'E', 'F' = 'G';
run;
proc means data=have noprint nway;
class year area / mlf;
format area $area_fmt.;
var profit;
output out=want sum(profit)=profit;
run;
I generally discourage have summaries inside data as you may end up getting things counted twice: sum of profit for the entire data set.
How will you be using that data set with the summaries included?
It will be used to display graphs on profit. No additional calculations will be made
Try MultiLabel Formats with PROC MEANS.
data have;
input Year Area $ Profit;
cards;
2001 A 1
2002 A 2
2001 B 1
2001 C 3
2002 C 1
2001 E 4
2002 E 2
2001 F 3
2002 F 4
;;;;
run;
proc format;
value $ area_fmt (multilabel)
'A' = 'A'
'B' = 'B'
'C' = 'C'
'A', 'B', 'C' = 'D'
'E' = 'E'
'F' = 'F'
'E', 'F' = 'G';
run;
proc means data=have noprint nway;
class year area / mlf;
format area $area_fmt.;
var profit;
output out=want sum(profit)=profit;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.