turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Creating rows in a data set based on sums of exist...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

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 |

Accepted Solutions

Solution

2 weeks ago

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BenBrady

2 weeks ago

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;
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BenBrady

2 weeks ago

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

2 weeks ago

It will be used to display graphs on profit. No additional calculations will be made

Solution

2 weeks ago

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BenBrady

2 weeks ago

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;
```