topic Re: Sum variable on different rows within id in SAS Programming

Sum variable on different rows within id

user40 — Wed, 02 Nov 2022 07:59:46 GMT

proc sql;
  create table solution as
  select lopenummer, daydiff , indiff, min, maks, sum(daydiff) as sum_daydiff 

  from   want2
  group by lopenummer , indiff;
quit;

I want to sum number of days within each ID and indiff, but this does not work. I only got

But the last row should have sum daydiff to 626 and not 85, 39 and 502 in three different rows. How can i get this?

Re: Sum variable on different rows within id

PeterClemmensen — Wed, 02 Nov 2022 08:09:16 GMT

Either indiff or lopenummer is not exactly equal across the three rows. My guess is that indiff is a calculated column and not exactly equal.

Re: Sum variable on different rows within id

user40 — Wed, 02 Nov 2022 08:24:13 GMT

It is equal.

Re: Sum variable on different rows within id

PeterClemmensen — Wed, 02 Nov 2022 08:27:00 GMT

How was indiff calculated?

Re: Sum variable on different rows within id

user40 — Wed, 02 Nov 2022 08:29:26 GMT

Indiff (days for each ami incidence for example) is a fast numeric variable and are not calculated. This variable is variable that are the same for the same incidence in each case.

Re: Sum variable on different rows within id

PeterClemmensen — Wed, 02 Nov 2022 08:40:27 GMT

Ok. Either that or you have to include min, max and so on in your group by clause.

I can't run your code because you did not post any sample data to work with.

Re: Sum variable on different rows within id

user40 — Wed, 02 Nov 2022 12:06:44 GMT

I try to sum daydiff variable in respect for lopenummer and indiff. Then I have this dataset,

data test; 
input lopenummer , indiff, min maks, daydiff 
ID1,  6495, 6130, 6860,  614 
ID2 7739, 7374, 8104, 19 
ID3, 9478, 9113, 9843, 85
ID3, 9478, 9113, 9843, 39
ID3, 9478, 9113, 9843, 502;
run;

I want to sum the daydiff variable regards group by lopenummer variable and indiff variable. I thinked that this would be like this:

proc sql; 
create table want as 
select lopenummer, indiff, min , maks, daydiff, 
from test 
order by lopenummer , indiff; 
quit;

But this do not work. What is wrong in my code?

Re: Sum variable on different rows within id

PaigeMiller — Wed, 02 Nov 2022 12:12:27 GMT

@user40 wrote:

Hi

I try to sum daydiff variable in respect for lopenummer and indiff. Then I have this dataset,
data test; 
input lopenummer , indiff, min maks, daydiff 
ID1,  6495, 6130, 6860,  614 
ID2 7739, 7374, 8104, 19 
ID3, 9478, 9113, 9843, 85
ID3, 9478, 9113, 9843, 39
ID3, 9478, 9113, 9843, 502;
run;
I want to sum the daydiff variable regards group by lopenummer variable and indiff variable. I thinked that this would be like this:

Would you please be so kind as to provide WORKING code for your data. This code doesn't work for a number of reasons, including a missing semi-colon.

proc sql; 
create table want as 
select lopenummer, indiff, min , maks, daydiff, 
from test 
order by lopenummer , indiff; 
quit;

But this do not work. What is wrong in my code?

I get an error in the LOG

1076  proc sql;
1077  create table want as
1078  select lopenummer, indiff, min , maks, daydiff,
1079  from test
           ----
           22
           76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS,
              BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE,
              NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

1080  order by lopenummer , indiff;
1081  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

so if you fix that by removing the semi-colon after daydiff, does it work then?

Re: Sum variable on different rows within id

user40 — Wed, 02 Nov 2022 12:16:35 GMT

data test; 
input lopenummer , indiff, min maks, daydiff 
ID1,  6495, 6130, 6860,  614 
ID2 7739, 7374, 8104, 19 
ID3, 9478, 9113, 9843, 85
ID3, 9478, 9113, 9843, 39
ID3, 9478, 9113, 9843, 502;
run;

proc sql; 
create table want as 
select lopenummer, indiff, min , maks, daydiff 
from test 
order by lopenummer , indiff; 
quit;

Now then?

Re: Sum variable on different rows within id

PaigeMiller — Wed, 02 Nov 2022 12:18:41 GMT

Reading the thread again, perhaps you want (instead of your SQL code) this:

proc summary data=test nway;
    class lopenummer;
    var indiff min maks daydiff;
    output out=want sum=;
run;

but as I don't have working data, I can't be sure that provides the answer you want.

Re: Sum variable on different rows within id

PaigeMiller — Wed, 02 Nov 2022 12:33:10 GMT

@user40 wrote:

data test; 
input lopenummer , indiff, min maks, daydiff 
ID1,  6495, 6130, 6860,  614 
ID2 7739, 7374, 8104, 19 
ID3, 9478, 9113, 9843, 85
ID3, 9478, 9113, 9843, 39
ID3, 9478, 9113, 9843, 502;
run;

Obviously, you did not actually run the code for TEST, a required step to meet the criterion that it is WORKING code. Please fix all the errors.