*Exercice1.a;
proc sql;
select Employee_ID, Employee_Gender,Salary, Birth_Date, Employee_Hire_Date, Employee_Term_Date,Marital_Status, Dependents
From orion.Employee_Payroll;
quit;


*Exercoce1.b;
proc sql;
select  Employee_ID, Employee_Gender,Marital_Status,Salary
from orion.Employee_Payroll;
quit;

*Exercice2;
proc sql;
select Employee_ID, Employee_Gender,Marital_Status,Salary,
Salary*1/3 as Tax
from orion.Employee_Payroll;
quit;

*ou //Select Employee_ID, Employee_Gender,Marital_Status, Salary/3 as Tax//

*Exercice3;
proc sql;
select Employee_ID,
case
when scan(Job_Title,-1," ")="Manager" then "Manager"
when scan(Job_Title,-1," ")="Director" then "Director"
when scan(Job_Title,-1," ")="Officer" then "Executive"
when scan(Job_Title,-1," ")="President" then "Executive"
else "N/A"
end as Level,
salary,

case
when (Salary)<52000 and (calculated Level)="Manager" then "Low"
when (Salary)<=72000 and (calculated Level)="Manager" then "Medium"
when (Salary)>72000 and (calculated Level)="Manager" then "High"

when (Salary)<108000 and (calculated Level)="Director" then "Low"
when (Salary)<=135000 and (calculated Level)="Director" then "Medium"
when (Salary)>135000 and (calculated Level)="Director" then "High"

when (Salary)<240000 and (calculated Level)="Executive" then "Low"
when (Salary)<=300000 and (calculated Level)="Executive" then "Medium"
when (Salary)>300000 and (calculated Level)="Executive" then "High"

else "N/A"
End as Salary_Range

From orion.Staff
Where calculated Level ne "N/A";
quit;


*proc sql;
*title "Salary Ranges For Orion Star Management";
*select Employee_ID,
case (scan(Job_Title,-1," "))
when "Manager" then "Manager"
when "Director" then "Director"
when "President" then "Executive"
else "N/A"
end as Level,
Salary,
case (calculated Level)
when "Manager" then
case
when (Salary>72000)then "High"
when (Salary>52000)then "Medium"
else "Low"
end
when "Director" then
case
when (Salary>135000)then "High"
when (Salary>108000)then "Medium"
else "Low"
end
when"Executive" then
case
when (Salary>300000)than "High"
;


*Create a list of personnel with salaries above 112000 include the employee identifier, job title and salary;
proc sql;
select Employee_ID, Job_title,Salary
From orion.Staff
where Salary>112000;
Quit;


*premiere façon;
proc sql;
   select Employee_Name, Employee_ID
      from orion.Employee_Addresses
      where Employee_Name contains ', N'
;
quit;


*deuxieme façon;
proc sql;
   select Employee_Name, Employee_ID
      from orion.Employee_Addresses
      where Employee_Name like '%, N';
quit;

*Troieme façon;
proc sql;
   select Employee_Name, Employee_ID
      from orion.Employee_Addresses
      where scan(Employee_Name, -1 ,",") like "%, N";
quit;


*Exercice4;
proc sql;
title "Cities Where Employees Live";
select distinct City
from orion.Employee_Addresses;
quit;

*Exercice5;
proc sql;
title "Donations Exceeding $90.00 in 2007";
select Employee_ID, Recipients,
SUM(Qtr1,Qtr2, Qtr3, Qtr4)as Total
from orion.Employee_donations
where calculated Total >90;
quit;

*Exercice6;
proc sql;
title "Employees who contributed 90%";
title2 "To Charitable companies";
title3 "That Are Also Incorporated(Inc.)";
select Employee_ID, Recipients
from orion.Employee_donations
where Recipients like '%Inc. 90/%%' escape '/' ;* tu veux que SAS considere qui est un vrai valeur au lieu d'un langage SAS;
quit;