Desktop productivity for business analysts and programmers

QUERY - Get count of row as percentage of column total

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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
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;    

View solution in original post


All Replies
Super User
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,

 

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

Solution
‎12-08-2017 09:44 AM
Super User
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 294 views
  • 1 like
  • 3 in conversation