topic Addition in column in SAS Programming

Addition in column

Don22 — Sat, 09 Jul 2022 15:49:17 GMT

Dear all,

I am a new user of SAS and struggling to find a solution for below case.

I want to calculate

1. Sum of values in column "case" corresponding to day 1-4 in column "Day"

2. Sum of values in column "case" corresponding to day 5-9 in column "Day" etc

Day1	Case
1	7
2	0
3	5
4	7
5	12
6	43
7	6
8	16
9	25
10	19
11	13
12	38
13	39
14	72
15	24
16	19
17	45
18	45
19	46
20	45

I have more than 200 rows and need to try different options by day period (1-5 days, 6-10 days or 1-7 days, 8-11 days etc)

I appreciate if you could teach me the SAS syntax to solve this.

Thanks

Don22

Re: Addition in column

PaigeMiller — Sat, 09 Jul 2022 16:21:20 GMT

I want to calculate

1. Sum of values in column "case" corresponding to day 1-4 in column "Day"

2. Sum of values in column "case" corresponding to day 5-9 in column "Day" etc

Okay, that would be easy if we stopped at day number 9. But what about the rest of the days, how do we group them? I don't see a pattern. What pattern of grouping days together do we follow for computing sums?

By the way, in order to display a pattern (at least to me) you need to show at least the first three elements, and possibly more than 3 elements, of the pattern.

Re: Addition in column

Tom — Sat, 09 Jul 2022 16:44:46 GMT

So you want to convert DAY into 5 day groups or 7 day groups etc?

Sounds like a task for simple arithmetic.

data want;
  set have;
  array group[5:10] group5-group10;
  do days=5 to 10;
    group[days]=ceil(day1/days);
  end;
run;

proc means data=want min max sum;
  ways 1;
  class group10-group5 ;
  var case;
run;

Re: Addition in column

PGStats — Sat, 09 Jul 2022 21:36:17 GMT

You can do this in a single step with proc SQL :

data have;
input Day1 Case;
datalines;
1   7
2   0
3   5
4   7
5   12
6   43
7   6
8   16
9   25
10  19
11  13
12  38
13  39
14  72
15  24
16  19
17  45
18  45
19  46
20  45
;

%let interval = 5;

proc sql;
/* create table want as */
select 
    1 + &interval.*floor((day1-1)/&interval.) as fromDay,
    &interval.*ceil((day1)/&interval.) as toDay,
    catx("-", calculated fromDay, calculated toDay) as label,
    sum('Case'n) as intervalCases
from have
group by fromDay, toDay, label;
quit

Note: The syntax 'Case'n tells SAS that Case is a variable name and not the reserved SQL word.

Re: Addition in column

Don22 — Sun, 10 Jul 2022 00:17:23 GMT

Thank you very much, could you please advise me how this syntax can be if I need to choose unequal interval? For example, I want the following output.

Interval	total cases
day 1-4	19
day 5-9	102
day 10	19
day 11-17	250
day 18-20	136

Thanks again.

Re: Addition in column

Don22 — Sun, 10 Jul 2022 00:21:58 GMT

Dear Tom,

Thank you for your response. I tried the syntax. But it is not working saying "A class or frequency variable is missing on every observation".

I'd like to have the following output, for example,

Interval	total cases
day 1- 4	19
day 5-9	102
day 10	19
day 11-17	250
day 18-20	136

Do you have any suggestions?

Thanks again.

Re: Addition in column

Tom — Sun, 10 Jul 2022 02:09:49 GMT

If there is no pattern to the intervals you will have to type them out.

If you want overlapping intervals then use a multilabel format and a procedure that knows how to use them.

data have;
  input Day1 Case ;
cards;
1 7
2 0
3 5
4 7
5 12
6 43
7 6
8 16
9 25
10 19
11 13
12 38
13 39
14 72
15 24
16 19
17 45
18 45
19 46
20 45
;

proc format ;
  value days (multilabel)
1-4 =' 1- 4'
5-9 =' 5- 9'
10-19='10-19'
11-17='11-17'
18-20='18-20'
;
run;

proc means data=have sum;
  class day1 / mlf ;
  format day1 days.;
  var case;
run;

The MEANS Procedure

 Analysis Variable : Case

           N
Day1     Obs             Sum
----------------------------
 1- 4      4      19.0000000

 5- 9      5     102.0000000

10-19     10     360.0000000

11-17      7     250.0000000

18-20      3     136.0000000
----------------------------

Re: Addition in column

Don22 — Sun, 10 Jul 2022 03:54:17 GMT

Thank you very much. That's exactly I want.

Thanks again