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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.