## QUERY - Get count of row as percentage of column total

Solved
Occasional Contributor
Posts: 18

# QUERY - Get count of row as percentage of column total

Hi,

I am a complete novice with SAS, but have experience with MySQL.

I am investigating some lost customers and want to compare:

- The country split of those that were lost

- The average country split of all customers

My plan is to take the customers table, count customers per country (which would be one column) and then show this as a percentage of total customers - IE what percent of total customers does each customer represent.

I have been searching but can only find references to doing this in summary tables. Is it possible to get a column like this in query, to use in later queries?

Thanks

Accepted Solutions
Solution
‎12-08-2017 09:44 AM
Super User
Posts: 9,818

## Re: QUERY - Get count of row as percentage of column total

Left join the count on rather than sub-query then:

```proc sql;
create table WANT as
select A.COUNTRY,
A.CUSTOMER_PER_COUNTRY,
B.TOTAL_CUSTOMERS,
A.CUSTOMER_PER_COUNTRY / B.TOTAL_CUSTOMERS * 100 as PCENT
from   (select COUNTRY,count(distinct CUSTOMER) as CUSTOMER_PER_COUNTRY from HAVE) A  left join (select count(distinct CUSTOMER) as TOTAL_CUSTOMERS from HAVE) B  on     1=1;
quit;    ```

All Replies
Super User
Posts: 9,818

## Re: QUERY - Get count of row as percentage of column total

Post example test data, in the form of a datastep using the code window (its the {i} above the post area)!

Otherwise we are just guessing what you want.  So at a guess:

```proc sql;
create table WANT as
select COUNTRY,
CUSTOMER_PER_COUNTRY,
(select count(distinct CUSTOMER) from HAVE) as TOTAL_CUSTOMERS,
CUSTOMER_PER_COUNTRY / calculated TOTAL_CUSTOMERS * 100 as PCENT
from   HAVE   group by COUNTRY;
quit;    ```

Occasional Contributor
Posts: 18

## Re: QUERY - Get count of row as percentage of column total

Sorry,

```PROC SQL;
CREATE TABLE WORK.QUERY_BASIC_COUNTRY_SPLIT AS
SELECT t1.COUNTRY,
/* CUSTOMERS */
COUNT(DISTINCT t1.CUST_NBR) AS CUSTOMERS,
(SELECT COUNT(DISTINCT t1.CUST_NBR) FROM WORK.QUERY_1A) AS TOTAL_CUSTOMERS,
CALCULATED CUSTOMERS / CALCULATED TOTAL_CUSTOMERS * 100 AS PCENT,
FROM WORK.QUERY_1A t1
GROUP BY t1.COUNTRY;
QUIT;```

If I include the TOTAL_CUSTOMERS or the PCENT lines the query runs so slow it does not complete.

Any help would be much appreciated.

Thanks

Solution
‎12-08-2017 09:44 AM
Super User
Posts: 9,818

## Re: QUERY - Get count of row as percentage of column total

Left join the count on rather than sub-query then:

```proc sql;
create table WANT as
select A.COUNTRY,
A.CUSTOMER_PER_COUNTRY,
B.TOTAL_CUSTOMERS,
A.CUSTOMER_PER_COUNTRY / B.TOTAL_CUSTOMERS * 100 as PCENT
from   (select COUNTRY,count(distinct CUSTOMER) as CUSTOMER_PER_COUNTRY from HAVE) A  left join (select count(distinct CUSTOMER) as TOTAL_CUSTOMERS from HAVE) B  on     1=1;
quit;    ```
Occasional Contributor
Posts: 18

## Re: QUERY - Get count of row as percentage of column total

RW9 wrote:

Left join the count on rather than sub-query then:

```proc sql;
create table WANT as
select A.COUNTRY,
A.CUSTOMER_PER_COUNTRY,
B.TOTAL_CUSTOMERS,
A.CUSTOMER_PER_COUNTRY / B.TOTAL_CUSTOMERS * 100 as PCENT
from   (select COUNTRY,count(distinct CUSTOMER) as CUSTOMER_PER_COUNTRY from HAVE GROUP BY COUNTRY) A  left join (select count(distinct CUSTOMER) as TOTAL_CUSTOMERS from HAVE) B  on     1=1;
quit;    ```

Thanks RW9, I amended that slightly, adding a GROUP BY, and it worked.

PROC Star
Posts: 1,334

## Re: QUERY - Get count of row as percentage of column total

Although it looks like you have what you desire, another option is to do it in "Summary Tables", and in the "Results" tab, tick the "Save results to a data set" tick box. That way, you'll end up with a dataset with your desired results that you can use in later programs.

Tom

☑ This topic is solved.