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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.