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
- /
- General Programming
- /
- How to SUM/aggregate dataset of records

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-19-2017 02:27 PM

Hi,

I am very new to SAS programming (Enterprise Guide) and have tried combining procedures like Proc SQL, Proc Freq as well as macros, but no code worth posting here because they are full of errors. I would greatly appreciate any help!

**Input:**

I have been given a dataset consisting of 8000 observations (really there are 80million, but that's not important for the context). Sample below.

ID is a variable but is not needed in the output.

For each observation ONE of the obs_1, obs_2 or obs_3 can have the value of 1, OR they are all 0.

Obs | data_period | ID | EUR | riskgrade | obs_1 | obs_2 | obs_3 |

1 | 200801 | a | 20 | 3 | 0 | 1 | 0 |

2 | 200801 | b | 30 | 3 | 0 | 1 | 0 |

3 | 200801 | c | 50 | 4 | 0 | 0 | 0 |

4 | 200802 | a | 40 | 3 | 1 | 0 | 0 |

5 | 200803 | a | 20 | 4 | 0 | 0 | 0 |

6 | 200803 | c | 10 | 4 | 1 | 0 | 0 |

… | … | … | … | … | … | … | … |

80000 | 201512 | c | 7 | 4 | 0 | 0 | 1 |

**Desired output:**

Sample below (made in Excel).

I need one table for EACH riskgrade (in the real dataset there are 20 different, i.e. I want 20 tables).

Each data_period should be assigned to one row.

OBS with riskgrade 3 | |||||

data_period | SUM_obs_1 | SUM_obs_2 | SUM_obs_3 | # of observations | SUM of EUR during data_period |

200801 | 0 | 2 | 0 | 2 | 50 |

200802 | 1 | 0 | 0 | 1 | 40 |

… | … | … | … | … | … |

Total (in sample) | 2 | 2 | 1 | 3 | 90 |

OBS with riskgrade 4 | |||||

data_period | SUM_obs_1 | SUM_obs_2 | SUM_obs_3 | # of observations | SUM of EUR during data_period |

200801 | 0 | 0 | 0 | 1 | 50 |

200803 | 1 | 0 | 0 | 2 | 30 |

… | … | … | … | … | … |

201512 | 0 | 0 | 1 | 1 | 7 |

Total (in sample) | 1 | 0 | 1 | 4 | 87 |

Accepted Solutions

Solution

10-24-2017
03:52 AM

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

Posted in reply to hermina

10-19-2017 02:42 PM - edited 10-19-2017 03:45 PM

PROC SUMMARY should do this.

Untested code

proc summary data=whatever; class riskgrade data_period; var eur obs_1 obs_2 obs_3; output out=want sum=sum_eur sum_obs_1 sum_obs_2 sum_obs_3 n(eur)=n; run;

--

Paige Miller

Paige Miller

All Replies

Solution

10-24-2017
03:52 AM

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

Posted in reply to hermina

10-19-2017 02:42 PM - edited 10-19-2017 03:45 PM

PROC SUMMARY should do this.

Untested code

proc summary data=whatever; class riskgrade data_period; var eur obs_1 obs_2 obs_3; output out=want sum=sum_eur sum_obs_1 sum_obs_2 sum_obs_3 n(eur)=n; run;

--

Paige Miller

Paige Miller

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

Posted in reply to PaigeMiller

10-19-2017 03:57 PM

@PaigeMiller Wow, many thanks for your quick response!

I tried out your simple yet effective code on my sample data and it works like a charm.

Will give it a go on my real data tomorrow.

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

Posted in reply to hermina

10-31-2017 03:54 PM

Hi there,

I have a similar issue to aggregate my data file. I tried your code, but not apply to my data. I wonder if you can help me out as well.

This is my data:

COHORT_YEAR | FG_Status | Needy_Status | Time2Degree | LocalRace | Gender | count |

2008 | N | N | Not graduated | American Indian or Alaska Nat | F | 1 |

2009 | N | N | Within5Years | American Indian or Alaska Nat | M | 1 |

2008 | N | Y | 4YorLess | American Indian or Alaska Nat | F | 1 |

2010 | N | Y | 4YorLess | American Indian or Alaska Nat | M | 1 |

2008 | N | Y | 4YorLess | American Indian or Alaska Nat | F | 1 |

2012 | N | Y | 4YorLess | American Indian or Alaska Nat | M | 1 |

2008 | N | Y | 4YorLess | American Indian or Alaska Nat | F | 1 |

2008 | N | Y | 4YorLess | American Indian or Alaska Nat | M | 1 |

I have 15000 cases and I need to sum/aggregate the Headcount variable given all the 6 categorical variables, which means the output data keeps the categorical variables, and can be used in Excel pivot tables. As I am learning SAS not for long and tried many ways, but none of them works for me. Hope that you can help me out. Thanks in advance.

Xiaowen Qin