Calcite | Level 5

## Sum variable on different rows within id

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

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

## Re: Sum variable on different rows within id

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

--
Paige Miller
10 REPLIES 10
Tourmaline | Level 20

## Re: Sum variable on different rows within id

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.

Calcite | Level 5

## Re: Sum variable on different rows within id

It is equal.

Tourmaline | Level 20

## Re: Sum variable on different rows within id

How was indiff calculated?

Calcite | Level 5

## Re: Sum variable on different rows within id

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.

Tourmaline | Level 20

## Re: Sum variable on different rows within id

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.

Calcite | Level 5

## Re: Sum variable on different rows within id

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?

Diamond | Level 26

## Re: Sum variable on different rows within id

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

--
Paige Miller
Diamond | Level 26

## Re: Sum variable on different rows within id

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

--
Paige Miller
Calcite | Level 5

## Re: Sum variable on different rows within id

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

Diamond | Level 26

## Re: Sum variable on different rows within id

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

--
Paige Miller
Discussion stats
• 10 replies
• 706 views
• 0 likes
• 3 in conversation