topic Re: Group and sum observations from two columns in SAS Programming

Group and sum observations from two columns

Thorius_Prime — Wed, 29 Aug 2018 12:31:07 GMT

Hi,

I have two columns. One is a column of numerical variables identifying the category of the asset (first number is main category, second number is subcategory eg. 31 is 3 for equity and 1 for common equity) the other column consists of numerical variables describing the total amount invested in the asset.

Example of dataset:
Id    total value
31   10000
23   9930
37   2892
26   6833

Desired result:
Category 2 Category 3
16763          12892

I need to sum every main category and present the data in a table.

So far I have created subsets of the data using the data process as seen below:

data data (keep=asset rename=(asset=asset_category));

set original_dataset;

if 71<=Assetnumber<=79 then; else delete;

I end up with 10 different data sets.

How do I sum + combine the data the smartest way?

Is there a more convienient way to handle the entire dataset?

regards,

Thorius

Re: Group and sum observations from two columns

PeterClemmensen — Wed, 29 Aug 2018 12:16:14 GMT

Always a good idea to post sample data. So this code is untested:

proc sql;
   create table want as
   select substr(put(Assetnumber, 2.), 1, 1) as Main_Category
         ,sum(amount) as Amount_Sum
   from original_dataset
   group by calculated Main_Category;
quit;

Re: Group and sum observations from two columns

RW9 — Wed, 29 Aug 2018 12:09:35 GMT

"the smartest way?" - start by posting some test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Then show what you want out from that data.

Re: Group and sum observations from two columns

Thorius_Prime — Wed, 29 Aug 2018 12:10:26 GMT

Nope. It's usually just a two digit number. It could be converted without any issue if that makes it easier to handle.

Re: Group and sum observations from two columns

Kurt_Bremser — Wed, 29 Aug 2018 12:14:47 GMT

Create a new group variable:

data intermediate;
set original;
groupvar = substr(put(asset,z2.),1,1);
run;

proc sql;
create table want as
select groupvar, sum(amount) as amount
from intermediate
group by groupvar;
quit;

Edit: changed the creation of groupvar.

Re: Group and sum observations from two columns

PaigeMiller — Wed, 29 Aug 2018 12:16:47 GMT

Something like this (although I agree with the others, you haven't shown us a dataset that matches the problem you describe in words)

proc summary data=have;
    class id;
    var total_value;
    output out=want sum=;
run;

Re: Group and sum observations from two columns

PeterClemmensen — Wed, 29 Aug 2018 12:16:59 GMT

Ok. Changed my response above 🙂

Re: Group and sum observations from two columns

Thorius_Prime — Wed, 29 Aug 2018 12:21:20 GMT

Added a data sample now 🙂

Re: Group and sum observations from two columns

s_lassen — Wed, 29 Aug 2018 12:22:57 GMT

I would consider using a format, something like this:

Proc format;
  value Asset
    0-<10='Junk'
   10-<20='Reasonable '
   20-<30='Sort of good'
  /* etc. for other categories */
    other='Unknown'
    ;
run;

proc summary data=have nway missing;
  class ID;
  format ID Asset.;
  var value;
  output out=want(drop=_TYPE_) sum=; 
run;

Re: Group and sum observations from two columns

PeterClemmensen — Wed, 29 Aug 2018 12:24:39 GMT

This code uses your sample data and creates the desired result:

data original_dataset;
input Id total_value;
datalines;
31 10000
23 9930
37 2892
26 6833
;

proc sql;
    create table want as
    select substr(put(Id, 2.), 1, 1) as Main_Category
    ,sum(total_value) as Amount_Sum
    from original_dataset
    group by calculated Main_Category;
quit;

Re: Group and sum observations from two columns

PaigeMiller — Wed, 29 Aug 2018 12:26:04 GMT

@Thorius_Prime wrote:
Added a data sample now 🙂

We need a data sample that matches the words you used to describe the problem. 😞