Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Welcome
- /
- All Things Community
- /
- SAS_Specific Conditional SUM

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-25-2017 12:02 PM
(3333 views)

Hi all,

I have a question on SAS Programming. It is about conditional sum. But it is very specific for me. Therefore, I want to ask as an example. I have the following dataset:

Group A Quantity

1 10 7

1 8 4

1 7 3

1 10 5

2 11 6

2 13 8

2 9 7

2 13 9

I want to add two more columns to this dataset. The new dataset should be:

Group A Quantity B NewColumn

1 10 7 10 12 (7+5)

1 8 4 10 12

1 7 3 10 12

1 10 5 10 12

2 13 6 13 15 (6+9)

2 10 8 13 15

2 9 7 13 15

2 13 9 13 15

So, the column B should be equal tha maximum value of each group and it is the same for all observations of each group. In this example, Group number 1 has 4 values. They are 10, 8, 7, 10. The maximum among these values is 10. Therefore, the values of the observations of the B column for the first group are all equal to 10. Maximum number for group number 2 is 13. Therefore, the values of the observations of the B column for the second group are all equal to 13.

The column C is more complicated. Its value depends on the all columns. Similiar to B column, it will be the same within group. More detailed, it is the sum of the specific observations of QUANTITIES column. These specific observations should belong to the observations that have the maximum value in each group. In our example, it is 12 for the first group. The reason is, the maximum number of first group is 10. and the quantities belong to 10 are 7 and 5. So, the sum of these is 12. For the second group it is 15. because the maximum value of the second group is 13 and the quantities belong to 13 are 6 and 9. So the sum is 15.

I hope. I can explain it. Many thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
Great explanation and nice presentation
data have;
input Group A Quantity;
cards4;
1 10 7
1 8 4
1 7 3
1 10 5
2 11 6
2 13 8
2 9 7
2 13 9
;;;;
run;quit;
data want;
retain group a quantity;
keep group a quantity max sum;
retain max beg end .;
do until(last.group);
set have;
by group;
if first.group then beg=quantity;
if a > max then max=a;
if last.group then do;
end=quantity;
sum=beg+end;
end;
end;
do until(last.group);
set have;
by group;
output;
end;
call missing(max, beg, end);
run;quit;
Up to 40 obs WORK.WANT total obs=8
Obs GROUP A QUANTITY MAX SUM
1 1 10 7 10 12
2 1 8 4 10 12
3 1 7 3 10 12
4 1 10 5 10 12
5 2 11 6 13 15
6 2 13 8 13 15
7 2 9 7 13 15
8 2 13 9 13 15
```

10 REPLIES 10

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
Great explanation and nice presentation
data have;
input Group A Quantity;
cards4;
1 10 7
1 8 4
1 7 3
1 10 5
2 11 6
2 13 8
2 9 7
2 13 9
;;;;
run;quit;
data want;
retain group a quantity;
keep group a quantity max sum;
retain max beg end .;
do until(last.group);
set have;
by group;
if first.group then beg=quantity;
if a > max then max=a;
if last.group then do;
end=quantity;
sum=beg+end;
end;
end;
do until(last.group);
set have;
by group;
output;
end;
call missing(max, beg, end);
run;quit;
Up to 40 obs WORK.WANT total obs=8
Obs GROUP A QUANTITY MAX SUM
1 1 10 7 10 12
2 1 8 4 10 12
3 1 7 3 10 12
4 1 10 5 10 12
5 2 11 6 13 15
6 2 13 8 13 15
7 2 9 7 13 15
8 2 13 9 13 15
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Amazing. Many thanks.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Just of note: @rogerjdeangelis's code will produce the wrong answer if there is a 3 or more way tie for the maximum value. e.g.:

data have; input Group A Quantity _B _NewColumn; cards; 1 10 7 10 13 (7+1+5) 1 10 1 10 13 (7+1+5) 1 8 4 10 13 1 7 3 10 13 1 10 5 10 12 2 13 6 13 15 (6+9) 2 10 8 13 15 2 9 7 13 15 2 13 9 13 15 ;

As such, I prefer to use proc sql for such calculations. The following can be written so that everything is nested within one call (i.e., not create a temporary file and then work from it), but I don't think either is more efficient than the other:

proc sql; create table need as select *, max(A) as B, (calculated B=A)*quantity as __NewColumn from have group by Group ; create table want (drop=_:) as select *, sum(__NewColumn) as NewColumn from need group by Group ; quit;

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Your description of the problem is far clearer than 90% of what gets posted here. Here's a possible solution using SQL:

```
data have;
input Group A Quantity;
datalines;
1 10 7
1 8 4
1 7 3
1 10 5
2 11 6
2 13 8
2 9 7
2 13 9
;
proc sql;
CREATE TABLE want AS
SELECT a.group, a.a, a.quantity, max(a.a) AS b,
sum(a.quantity * (a.a = b.max_a)) AS new_column
FROM have AS a
LEFT JOIN (SELECT group, max(a) AS max_a
FROM have
GROUP BY group) AS b
ON a.group = b.group
GROUP BY a.group;
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Many thanks. I cannot check it now. I will check it tomorrow. If it works, i will accept it. Many thanks again for your time.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I guess I read the problem wrong, I thought the op wanted the first + last as sum

not the associated with values..

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Many thanks for your comments. My question is, Assume that I will use sql and create table. Then, will it be seen like a table or dataset? The reason is, I will further work on that datasets. Therefore, I need datasets.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Many thanks. I will check it and reply tomorrow. It will very helpful for me.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

This is untested (I'm away from sas) and will probably have mistakes, but a triple DoW loop should work. One pass to get the max per group, second pass to get the sum, third pass to output the records. Something like:

```
data want ;
do until(last.group) ;
by group ;
set have ;
B=max(A,B) ;
end ;
do until(last.group) ;
set have ;
by group ;
if A = B then NewColumn = sum(NewColumn, Quantity) ;
end;
do until(last.group);
set have ;
by group;
output ;
end ;
run;
```

BASUG is hosting ** free webinars ** Next up: ** Mike Sale ** presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

**Don't miss out on SAS Innovate - Register now for the FREE Livestream!**

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

Upcoming Events

- SAS Innovate | 16-Apr-2024
- From Zero to SAS: Using SAS Tasks to Summarize and Manipulate Your Data | 25-Apr-2024
- Free Webinar from Michigan SAS User Group | 25-Apr-2024
- How Can You Use Deep Learning for Personalized Pricing and Revenue Optimization? | 02-May-2024
- SAS® Enterprise Guide® Is Now Integrated With SAS® Viya® 4 | 07-May-2024
- Iowa SAS Users Group: The 14th One-Day Conference | 13-May-2024
- Nebraska SAS Users Group (NEBSUG) One-Day SAS Conference | 14-May-2024