topic Re: Sum values by classes and format the output in New SAS User

Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 13:27:24 GMT

Hi guys,

suppose to have the following:

data DB1;
input cat1 cat2 cat3 cat4 cat5 place value;
cards;
0 0 0 0 0 1 125
0 0 0 0 0 2 33
0 0 0 0 0 3 44
0 0 1 0 0 1 2
0 0 1 0 0 2 .
0 0 1 0 0 3 1
0 1 0 0 0 1 .
0 1 0 0 0 2 .
0 1 0 0 0 3 .
0 1 0 1 0 1 65
0 1 0 1 0 2 36
0 1 0 1 0 3 43
0 1 1 0 0 1 .
0 1 1 0 0 2 .
0 1 1 1 0 1 4
0 1 1 1 0 2 2
1 0 0 0 0 1 164
1 0 0 0 0 2 516
1 0 0 0 0 3 619
1 0 1 0 0 1 5
1 0 1 0 0 2 1
1 0 1 0 0 3 2
;

Is there a way to generate this desired output?

data DB2;
input CAT Place Value ;
cards;
0 1 125

0 2 33

0 3 44

1 1 169

1 2 517

1 3 621

2 1 69

2 2 38

2 3 43

3 1 7

3 2 1

3 3 3

4 1 69

4 2 38

4 3 43

5 1 0

5 2 0

5 3 0
;

In other words, I would like to sum all values in column value for each cat* by place 1,2,3. It doesn't matter if cat* are not mutually exclusive. Moreover, the output should be presented as a single column of cat stratified by "place". Can you help me please?

Edit:

looking at the cat5: it has all "0s". As a rule, if all the other cat*(s) in DB1 have 0 (i.e., no "1" index), in DB2 "CAT" will be number "0". This means that cat5 specific values will be 0 because no "1" index is found for that cat* in DB1. Conversely it will be in CAT "0" for values 125, 33 and 44 because all the other cat* are 0 (no "1" index in DB1). Based on this, in DB2 we will see CAT 5 = 0 for place 1,2 and 3 because no specific values are found for cat5 (corresponding to "1" in DB1). For all the other cases, simply a sum of "value" is required for each "CAT*" in DB1 by "place".

Example: let's consider cat1 column in DB1 that will become CAT = 1 in DB2. If we look at all times we see "1"index, for place 1 we will have values in DB1 equal to: 164 and 5. I just need to sum them to get 169. For place 2 we have: 516 and 1 that after the sum will become 517 and for place 3 we have 619 and 2 that after the sum will become 621. This is the output I need.

Note: the sum must be performed REGARDLESS what happens in the other "cat*" in DB1 except for 0 as explained above.

Re: Sum values by classes and format the output

PaigeMiller — Tue, 08 Aug 2023 12:08:48 GMT

I don't understand the relationship of CAT in DB2 (which has values 1 to 5) to the data in DB1. Please explain further how CAT=1 in DB2 is derived from DB1. Please explain further how CAT=2 in DB2 is derived from DB1. And so on.

Re: Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 12:18:11 GMT

So, basically there are 5 cat* that in DB1 are on columns while in DB2 will be in one column with indexes from 1 to 5 based on the column name cat1, cat2, cat3, cat4, cat5 in DB1

Re: Sum values by classes and format the output

PaigeMiller — Tue, 08 Aug 2023 12:23:42 GMT

I'm afraid this really doesn't explain the issue. The values for CAT5 are all zeros. How is there a CAT = 5 in the output?

Re: Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 12:35:43 GMT

So: there are no patients in category 5 for all the three places (1,2,3) with a value in value column (otherwise you should see "1" in the column cat5)regardless any value in cat*(1-4). Despite this, cat5 must appear in DB2 to say "we cannot find patients in cat5 with a value different from 0 for all the three places". In DB1 categories are listed in columns as: cat*(1-5) while in DB2 from 1 to 5

Re: Sum values by classes and format the output

PaigeMiller — Tue, 08 Aug 2023 12:50:43 GMT

Here's the problem. In order to write code, you have to know the inputs, which you have provided; and you have to know the outputs from those inputs (which you have not provided because your outputs have values for CAT = 5 and so this does not correspond to the inputs), and you have to know the logic to get from inputs to outputs. So I'm stuck, and I'm afraid I still don't understand what the method is and I still don't understand the method to get from inputs to outputs.

And even if you explain the CAT5 issue, its still not clear to me how to get the other categories either. Please explain in words the whole thing. Step by step examples are good. Do not make me guess what to do. Do not make me guess what the steps are.

Re: Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 12:55:19 GMT

Ok, I understand the point. I will edit the post

Re: Sum values by classes and format the output

PaigeMiller — Tue, 08 Aug 2023 13:03:51 GMT

@NewUsrStat wrote:
Ok, I understand the point. I will edit the post

Please do not modify the original post. Please explain in a reply. Otherwise, if you modify the original post, then the thread makes no sense to anyone.

Or start a brand new thread.

Re: Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 13:09:54 GMT

I just added an explanation. Hope it is more clear

Re: Sum values by classes and format the output

PaigeMiller — Tue, 08 Aug 2023 13:32:59 GMT

Okay, I think I understand now. Thanks.

This is a brute force solution, but it seems to work.

%macro do_this;
    proc delete data=db2; run; quit;
    %do i=1 %to 5;
        proc summary data=db1 nway;
            class place;
            var value;
            weight cat&i;
            output out=out sum=;
        run;
        data out;
            set out;
            cat=&i;
        run;
        proc append base=db2 new=out;
        run;
        proc delete data=out; run; quit;
    %end;
%mend;
%do_this

Re: Sum values by classes and format the output

Tom — Tue, 08 Aug 2023 13:34:39 GMT

Sounds like CAT1 to CAT5 are BOOLEAN flags. Perhaps meaning that Category 1 (or 2 or ...) was present.

Sounds like the goal is to find the maximum category that has present?

data have;
  set want;
  array cats cat1-cat5;
  cat=0;
  do index=1 to dim(cats);
     if cats[index] then cat=index;
  end;
  drop index;
run;

Re: Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 13:53:21 GMT

Really thank you very much!!!!! It works perfectly!

Re: Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 13:54:37 GMT

Not exactly Tom. Just to sum values by place (1-3) for each category with the exception of all "0" in all categories in DB1 as I explained in the post

Re: Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 13:57:43 GMT

Sorry but why "0" disappears in the output. I need

Re: Sum values by classes and format the output

Tom — Tue, 08 Aug 2023 14:11:35 GMT

@NewUsrStat wrote:
Not exactly Tom. Just to sum values by place (1-3) for each category with the exception of all "0" in all categories in DB1 as I explained in the post

To SUM a set of variable in a single observation just use the SUM() function.

data want ;
  set have;
  cat = sum(of cat1-cat5);
run;

To SUM a series of variables just use PROC SUMMARY (aka PROC MEANS).

proc summary data=have;
   output out=want sum(cat1-cat5)= ;
run;

If you want separate sums for each value of PLACE then you could add PLACE as a CLASS variable.

But you will still have five variables named CAT1 to CAT5 that now have the sums.

I do not see how that would get you a single variable named CAT. Unless you wanted to TRANSPOSE the data so that you have 5 observations.

proc summary nway data=have;
  class place;
  var cat1-cat5 ;
  output out=wide sum= ;
run;

proc transpose data=wide out=tall;
  by place;
  var cat1-cat5 ;
run;

And you have not explained what role that last variable VALUE has in this at all.

Re: Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 14:41:40 GMT

Thank you Tom, but still "0" remains out. I also need to count (or sum) when all cat* have "0" to get 125, 33, 44. This output seems to disappear but I need to show numbers when no one has "1" in DB1

Re: Sum values by classes and format the output

Tom — Tue, 08 Aug 2023 18:00:39 GMT

@NewUsrStat wrote:
Thank you Tom, but still "0" remains out. I also need to count (or sum) when all cat* have "0" to get 125, 33, 44. This output seems to disappear but I need to show numbers when no one has "1" in DB1

I have no idea what that means.

Please create a small precise example of input and the results you want. Explain in detail how you get that results from that input. Spell out the STORY. What do the CAT1 to CAT5 variables represent? What do the other two variables represent? What to the observations in the input represent? What do the observations in the output represent? What do the variables in the output represent?

Are you asking to create variables other than the 5 that exist in the data? How do you know how many variables there could be?

Re: Sum values by classes and format the output

NewUsrStat — Tue, 08 Aug 2023 18:31:51 GMT

So, regarding CAT =0 I mean just to add the frequencies when all cat*(s) in DB1 are 0 (no "1" index). This means to report 125, 33 and 44 relative to place 1, 2 and 3 respectively in the output you provided.

Re: Sum values by classes and format the output

Ksharp — Wed, 09 Aug 2023 11:27:45 GMT

data DB1;
input cat1 cat2 cat3 cat4 cat5 place value;
cards;
0 0 0 0 0 1 125
0 0 0 0 0 2 33
0 0 0 0 0 3 44
0 0 1 0 0 1 2
0 0 1 0 0 2 .
0 0 1 0 0 3 1
0 1 0 0 0 1 .
0 1 0 0 0 2 .
0 1 0 0 0 3 .
0 1 0 1 0 1 65
0 1 0 1 0 2 36
0 1 0 1 0 3 43
0 1 1 0 0 1 .
0 1 1 0 0 2 .
0 1 1 1 0 1 4
0 1 1 1 0 2 2
1 0 0 0 0 1 164
1 0 0 0 0 2 516
1 0 0 0 0 3 619
1 0 1 0 0 1 5
1 0 1 0 0 2 1
1 0 1 0 0 3 2
;

proc sql;
create table want as
select 0 as cat,place,sum(value) as value from DB1 where sum(cat1,cat2,cat3,cat4,cat5)=0 group by place
union
select 1,place,sum(cat1*value) from DB1 group by place
union
select 2,place,sum(cat2*value) from DB1 group by place
union
select 3,place,sum(cat3*value) from DB1 group by place
union
select 4,place,sum(cat4*value) from DB1 group by place
union
select 5,place,sum(cat5*value) from DB1 group by place
;
quit;