Sum variable on different rows within id

[user40]

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? 

[PeterClemmensen]

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.

[user40]

It is equal.

[PeterClemmensen]

How was indiff calculated?

[user40]

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.

[PeterClemmensen]

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.

[user40]

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: 

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? 

[PaigeMiller]

---

@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?

[PaigeMiller · Accepted solution]

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.

[user40]

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?

[PaigeMiller]

---

@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.