Code

/*MACRO VARIABLES TO PASS USERNAME AND PASSWORD(S) TO ACCESS TARGET DATA SOURCES USED FOR TEST OBJECTIVE(S) */

%LET USR = XXXX;
%LET PWD = XXXX;

/*ENTER THE SAS GRID DIRECTORY WHERE OUTPUT FILES ARE TO BE STORED */
LIBNAME PROJECT'/TEST/GLOBAL_SUPER_STORE';

%LET location=/C:/TEST/;
%LET file=global_super_store_2016.xlsx ;  /*Enter the filename */

/*CONNEC TO DATA SOURCE */

/*1. Import 'Global SuperStore' Order 1-4, Returns and people*/

PROC IMPORT
      OUT = Orders
    DATAFILE= "&location.&file."
    DBMS=xlsx REPLACE;
      Sheets =Orders;
    GETNAMES = YES; 
RUN;

PROC IMPORT
     OUT = Orders2
    DATAFILE= '&location.&file."
    DBMS=xlsx replace;
     Sheets=Orders2;
    GETNAMES = YES;
RUN;

PROC IMPORT
     OUT = Orders3
    DATAFILE= '&location.&file."
    DBMS=xlsx replace;
     Sheets=Orders3;
    GETNAMES = YES;
    
PROC IMPORT
     OUT = Orders4
    DATAFILE= '&location.&file."
    DBMS=xlsx replace;
     Sheets=Orders4;
    GETNAMES = YES;
    
PROC IMPORT
     OUT =  Returns
    DATAFILE= '&location.&file."
    DBMS=xlsx replace;
     Sheets=Returns;
    GETNAMES = YES;    
 
 PROC IMPORT
     OUT =  People
    DATAFILE= '&location.&file."
    DBMS=xlsx replace;
     Sheets=People;
    GETNAMES = YES;    
    
/*2. Append all orders tab and remove duplicates*/
proc sql;
create table answer2 as 
select 
      * from Orders
Union
select 
      * from Orders2
;

/*3. Extract the first 2 letters of Order ID and create a new column of State.*/
proc sql;
create table answer3 as
select 
      substr(OrderID, 1,2,) as state1
from answer2
;

/4. Make 2 columns, first name and last name from the CustomerName column*/
proc sql;
create table answer4 as
select 
      scan(CustomerName,1, ' ') as First_Name,
      scan(CustomerName,2, ' ') as Last_Name
from answer2
;

/5. Create a new column 'OrderDate_Plus_2' by adding 2 days to Order Date.*/
proc sql;
create table answer5 as
select 
      OrderDate,
      intnx('day', OrderDate, 2)as OrderDate_Plus_2 FORMAT MMDDYY10. 
from answer2
;

/*6. Find number of days between orderdate and ship date*/
proc sql;
create table answer6 as
select 
      OrderDate,
      ShipDate,
      intck('day', OrderDate, ShipDate) as Day_diff
from 
      answer2
;

/*7. Create a new column 'Flag' if the day difference is more than 2 days*/
proc sql;
create table answer7 as
select
      *,
      Case
            when Day_diff>=2 then 'more than 2'
            else ' '
      End as Flag
from 
      answer6
;

/*8. Extract year from orderdate*/
proc sql;
create table answer8 as
select 
      OrderDate,
      YEAR(OrderDate) AS Order Year
from 
      answer6
;

/*9. Create a new column called ID with: first letter of First Name, First Letter of Second Name, -, first two letters of order id */
proc sql;
create table answer9a as
select
      *,
      scan(CustomerName,1, ' ') as First_Name,
      scan(CustomerName,2, ' ') as Last_Name,
      substr(OrderID, 1,2) as state2
from answer2
;

proc sql;
create table answer9b as
select
      First_Name,
      Last_Name,
      OrderID,
      state2,
      CATS(substr(First_Name,1,1), substr(Last_Name,1,1), '-', state2) as
Combined
from answer9a
;

/*10. Drop St, ID, Year*/
data answer10;
set answer2 (drop=OrderID);
run;

/*11. Join the order and return table and remove the duplicates*/
proc sql;
create table answer11 as
select
      distinct
      *
from
      Orders a
      left join Returns b
      on a.OrderID=b.OrderID
;
/*verify it indeed distinct, by checking the left table's distinct number*/
      proc sql;
      create table answer11a as
      select 
            distinct
            *
      from Orders;
    
/*12. Select the how many times a combination of country and city appears and sort it by decreasing number */
proc sql;
create table answer12 as 
select 
      Country,
      City,
      count(*) as count_comb
from Orders
group by Country, City
order by count_comb desc
;

/*13. Select the combination of country and city which has the total sum more than the average of all Country and city.*/
proc sql;
create table answer13a as
select
      *,
      avg(count_comb) as avg_of_city
from answer12;

proc sql;
create table answer13 as
select
      *
from answer13a
where avg_of_city < count_comb
order by count_comb desc;

      
/*below is one step, with sub-query*/
proc sql;
create table answer13b as
select 
      *
from answer12, (select avg(count_comb) as avg_of_city from answer12)
where avg_of_city < count_comb
order by count_comb desc
;