# Help with Summary calculations

#### ahart

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

#### hlsmith

##### Not a robit
Well I suck at proc sql, and using that procedure would have been my recommendation. Does your code not work?

#### kdt

##### New Member
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
last_touch_channel,
count(distinct last_touch_channel) as n,
ifC(calculated n eq 2,'Both',last_touch_channel)  as x
from have

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

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

quit;

#### kdt

##### New Member
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
last_touch_channel,
count(distinct last_touch_channel) as n,
ifC(calculated n eq 2,'Both',last_touch_channel)  as x
from have

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