Help with Summary calculations

#1
hi,

I am working from the file attached.

I would like to create a summary by last_touch_channel for all customers who purchased more than once (so have to be "existing" as their new_status ;

1) only ever purchased on desktop - how many customers and what total Revenue_Pre_Cart_Discount?
2) only ever purchased on mobile - how many customers and what total Revenue_Pre_Cart_Discount?
3) purchased on desktop and mobile - how many customers and what total Revenue_Pre_Cart_Discount?

I would really appreciate a steer as I started on the code but can;t seem to get it right.:tup::)
See below

proc sql;
create table Multidevice as
Select

Last_Touch_Channel,
COUNT(DISTINCT Case When Last_Touch_Channel='desktop' ) as desktop only,
COUNT(DISTINCT Case When Last_Touch_Channel='mobile' ) as mobile only

From Example

Group by

Last_Touch_Channel

;
quit;
 

kdt

New Member
#3
As this takes different levels of summary, I've done it in 3 parts here.

Generally speaking, SAS works better for normalized data, but you can transpose the results if needed.

Code:
/*Example data*/
data have;
infile cards missover ;
input EmailAddress :$20. 	Last_Touch_Channel :$10.	Revenue_Pre_Cart_Discount	8.;
cards ;
test@HOTMAIL.COM mobile  100
test@HOTMAIL.COM desktop 100
test@HOTMAIL.COM mobile  100
test1@HOTMAIL.COM mobile 100	
test3@HOTMAIL.COM desktop 100	
test3@HOTMAIL.COM mobile 100	
;;;;
run;

 proc sql ;
   create table card0 as
      select distinct emailaddress,
             last_touch_channel,
             count(distinct last_touch_channel) as n,
             ifC(calculated n eq 2,'Both',last_touch_channel)  as x
      from have
      group by emailaddress  ;

   create table card1 as
      select h.*,c.x, c.n
      from have h left join
      (select distinct emailaddress, x, n from card0) c
      on h.emailaddress = c.emailaddress;

   create table device as
      select  distinct  x, n,
              sum(REVENUE_PRE_CART_DISCOUNT) as total_revenue
      from card1 ;

   quit;
 

kdt

New Member
#4
Something like this:

1) First categorize subjects based mobile, desktop, or both
2) merge that data back to the original data
3) summarize the data by category

You can transpose if needed at the end, but generally its easier to manipulate if normalized

Code:
data have;
infile cards missover ;
input EmailAddress :$20. 	Last_Touch_Channel :$10.	Revenue_Pre_Cart_Discount	8.;
cards ;
test@HOTMAIL.COM mobile  100
test@HOTMAIL.COM desktop 100
test@HOTMAIL.COM mobile  100
test1@HOTMAIL.COM mobile 100	
test3@HOTMAIL.COM desktop 100	
test3@HOTMAIL.COM mobile 100	
;;;;
run;



proc sql ;
   create table card0 as
      select distinct emailaddress,
             last_touch_channel,
             count(distinct last_touch_channel) as n,
             ifC(calculated n eq 2,'Both',last_touch_channel)  as x
      from have
      group by emailaddress  ;

   create table card1 as
      select h.*,c.x, c.n
      from have h left join
      (select distinct emailaddress, x, n from card0) c
      on h.emailaddress = c.emailaddress;

   create table device as
      select  distinct  x, n,
              sum(REVENUE_PRE_CART_DISCOUNT) as total_revenue
      from card1 ;

   quit;