topic Re: QUERY - Get count of row as percentage of column total in SAS Enterprise Guide

QUERY - Get count of row as percentage of column total

jagnew — Fri, 08 Dec 2017 10:03:51 GMT

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

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

RW9 — Fri, 08 Dec 2017 10:20:55 GMT

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;

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

jagnew — Fri, 08 Dec 2017 11:10:01 GMT

Sorry,

Here is the code I am now trying, following your reply:

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

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

RW9 — Fri, 08 Dec 2017 11:49:14 GMT

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;

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

TomKari — Fri, 08 Dec 2017 12:32:28 GMT

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

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

jagnew — Fri, 08 Dec 2017 14:43:31 GMT

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