Handle Transaction Data 2

created on Aug. 31, 2019, 10:45 p.m.

filename reffile '/home/khanhpham0/Project/Handling Transactional Data/Transactional data.xlsx';

proc import datafile=reffile out=sport_sale dbms=xlsx replace;
    range="input$A2:C2000";
    getnames=yes;
    format 'time stamp'n datetime16.;
run;

/* 1.     Number of times customer 128 purchased */

proc sql;
    title 'Number of times customer 128 purchased';
    select distinct 'customer id'n, count('customer id'n) as 
     "Number_of_times_purchased"n from work.sport_sale where 'customer id'n=128;

/* 2.  Number of customers who bought Cricket Bat MRF  also bought Tennis Racket Wilson in entire time period */
select count(*) as 'Customer_bought_both'n
    from
    (
    select distinct 'customer id'n
    from sport_sale
    where 'product name'n = 'Cricket Bat MRF' 
    intersect
    select distinct 'customer id'n
    from sport_sale
    where 'product name'n ='Tennis Racket Wilson'
    ); 

/* 4.  Which day of the week is most popular in terms of total number of products sold */
select *
    from      
    (

    select put(datepart('time stamp'n),weekdate9.) as DayofWeek, count(*) as Quantity_Sold
    from work.sport_sale
    group by dayofweek
    )

group by dayofweek
having Quantity_Sold=max(Quantity_Sold);

/* 5.       Which day of the week is most popular for Helmet */
select *
from
(

select put(datepart('time stamp'n),weekdate9.) as WeekofDay,'product name'n, count(*) as Quantity_Sold
from work.sport_sale
where 'product name'n = 'Helmet'
group by weekofday, 'product name'n
 )

having Quantity_Sold=max(Quantity_Sold);

/* 6.       Which month has highest number of products sold */
select *
from
(select put(datepart('time stamp'n), monname.) as Month, count(*) as Quantity_sold
from sport_sale
group by Month
)
having Quantity_Sold=max(Quantity_Sold);

/* 7.       Which  month has highest number of Feather Ball Yonex sold */
    select * from
(select put(datepart('time stamp'n), monname.) as Month, count(*) as
        Quantity_sold from sport_sale where 'product name'n='Feather Ball Yonex'
        group by Month) having Quantity_Sold=max(Quantity_Sold);

quit;

 

 

/* 8.  If you have 4 time periods of the day (  Day parts: 9am to 12 noon as Morning ; 12 noon to 3pm afternoon and 3pm to 6pm evening ) */

proc sql;
    create table time as select hour(timepart('time stamp'n)) as Hour, count(*) as
        Quantity_sold from work.sport_sale group by Hour;

quit;

proc format;
    value timefmt 9-12="In the morning" 13-15="In the afternoon"
        15<-17="In the evening";
run;

data time;
    set time;
    format Hour timefmt.;
run;

proc tabulate data=work.time;
    var quantity_sold;
    class Hour;
    table Hour, quantity_sold * sum="Total";
run;