Try Yourself and comment output
Note:If Required Change The Library Name.....
/*1.Create a new variable for annual salary from monthly salary. */
data annual_sal;
set prog1.sales;
Annual_Salary=Salary*12;
format Annual_Salary comma12.2;
run;
proc print;run;
/*2.Filter employees who joined after 2000*/
data staff;
set prog1.staff1;
where Emp_Hire_Date > '31dec1999'd;
run;
proc print;run;
/*3.Calculate sales totals for each product. (SALES)*/
proc sql;
select product_id,sum(total_retail_price) as total format=dollar12.0
from prog1.order_fact
group by Product_ID;
quit;
/*4.Derive age from date of birth. (EMPLOYEE)*/
data sales;
set prog1.sales;
format Birth_Date date9.;
age=intck('year',birth_date,'16may2025'd);
run;
proc print;run;
data sales;
set prog1.sales;
format Birth_Date date9.;
Age = int(("16may2025"d - birth_date) / 365.25);
run;
proc print data=sales;
run;
/*5.Merge employee data with their attendance. (EMPLOYEE, EMP_ATTENDANCE)*/
proc sort data=prog1.employee_addresses out=emp1;
by employee_id;
run;
proc sort data=prog1.employee_donations out=emp2;
by employee_id;
run;
data emp3;
merge emp1 (in=a) emp2(in=B);
by employee_id;
if a and b;
run;
proc print;run;
data emp_attendance;
input Employee_ID Date :date9. Status $;
format Date date9.;
datalines;
120261 01JAN2023 Present
120261 02JAN2023 Absent
120261 03JAN2023 Present
120262 01JAN2023 Present
120262 02JAN2023 Present
120262 03JAN2023 Absent
120263 01JAN2023 Present
120263 02JAN2023 Present
120264 01JAN2023 Absent
120264 02JAN2023 Present
120265 01JAN2023 Present
120265 02JAN2023 Present
120266 01JAN2023 Absent
120267 01JAN2023 Present
120268 01JAN2023 Absent
;
run;
proc sort data=emp_attendance out=emp4;
by employee_id;
run;
data emp5;
merge emp1(in=a) emp4(in=b);
by employee_id;
if a and b;
run;
proc print;run;
/*6.Subset sales data to show only high-value transactions. (SALES)*/
proc means data=prog1.order_fact mean;
var total_retail_price;
run;
data high;
set prog1.order_fact;
where total_retail_price > 162;
run;
proc print;run;
/*7.Sort employee data by department(job_title) and salary. (EMPLOYEE)*/
proc sort data=prog1.nonsales out=nonsales;
by job_title descending salary;
run;
proc print;run;
/*8.Count number of employees in each department. (EMPLOYEE)*/
proc sql;
select job_title,count(job_title) as count
from prog1.nonsales
group by job_title;
quit;
proc freq data=prog1.nonsales;
tables job_title / nocum nopercent;
run;
/*9.Replace missing salary values with department average. (EMPLOYEE)*/
proc sql;
create table avgsal as
select job_title,avg(salary) as avgsal
from prog1.nonsales
group by job_title;
quit;
proc sort data=prog1.nonsales out=nonsales;by job_title;run;
proc sort data=avgsal;by job_title;run;
data nonsales;
merge avgsal(in=a) nonsales(in=b);
by job_title;
if missing(salary) then salary= avgsal;
run;
proc print;run;
/*10.Create a cumulative sales column. (SALES)*/
data sales;
set prog1.price_current;
retain cumulative 0;
cumulative + unit_cost_price;
run;
proc print;run;
libname Prog2 "C:\Users\Lenovo\Desktop\NEW\New folder\PROG 2\datasets\PROG2 DATASETS\PROG2 DATASETS";
/*11.List customers who purchased more than 5 products.*/
/* Step 1: Count number of products purchased per customer */
proc sql;
create table customers1 as
select customer_id,count(product_id) as count_purchased
from prog1.order_fact
group by customer_id
having count(product_id) > 5;
quit;
proc print;run;
/* Step 2: Optionally join with customer info */
proc sql;
create table customer_details as
select a.customer_id,b.customer_name,a.count_purchased
from customers1 as a left join
prog1.customer as b
on a.customer_id=b.customer_id;
quit;
proc print;run;
/*alternate method*/
proc sort data=prog1.order_fact out=fact;
by customer_id;
run;
data fact1;
set fact;
by customer_id;
if first.customer_id then count=0;
count+1;
if last.customer_id then output;
keep customer_id count;
run;
proc print;run;
data fact2;
set fact1;
if count > 5;
run;
proc print;run;
proc sort data=prog1.customer out=customer1;
by customer_id;
run;
data customer;
merge customer1(in=a) fact2(in=b);
by customer_id;
if b;
keep customer_id customer_name count;
run;
proc print;run;
No comments:
Post a Comment