CODEWITHSUNDEEP
sqlsas
Neoleogeo
Neoleogeo

Reputation: 323

How to summarize information into different columns by condition?

I am using sql (in sas through procsql ) to get a table.

I managed to join a bunch of different tables and get all the information into one but I do not know how to do the last step.

I Have 3 columns. Segment, Country, Revenue.

Segment | Country|Revenue|
A       |   USA  |   3   |
A       |   CAN  |   2   |
A       |   FRA  |   1   |
A       |   ITA  |   2   |
.
.
.

What I need is to sum the revenue by categorizing this countries into continents: Like this

Segment | America| Europe|
A       |   5    |   3   |
B       |   7    |   10  |

I honestly don't know how to start. I could create two tables, using where to filter by country, and then remerging, but I was wondering if there is a more straight forward way.

Upvotes: 1

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use conditional aggregation. If you have a hard list of countries:

proc sql;
    select segment, sum(case when country in ('USA', 'CAN', . . .) then revenue end) as americas,
           sum(case when country in ('FRA', 'ITA', . . . ) then revenue end) as europe
    from t
    group by segment;

Upvotes: 1

Related Questions