BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jagnew
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;    

 

jagnew
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;    
jagnew
Obsidian | Level 7

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

TomKari
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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