Work Example-Weekly Report

created on Feb. 7, 2020, 3:37 p.m.

%macro importexcel(libname=,file=,name=);
%let path=/home/khanhpham0/ISS-Work/Biglist 201860/;
filename reffile "&path&file";
proc import datafile=reffile
dbms=XLSX
out=&libname..&name
replace;
sheet="IN-PA";       
%mend importexcel;

---

/* import excel file */

%importexcel(libname=work,file=Biglist 10.01.2018.xlsx,name=biglist01);

%importexcel(libname=work,file=Biglist 10.08.2018.xlsx,name=biglist08);

---

/*merge data using data step*/
%macro m_sort_merge(id=,libname=,thisweek=,lastweek=,joindata=, type=);
/* sorted thisweek data */
proc sort data=&libname..&thisweek.;
by &id;
run;
/*sorted lastweek data*/
proc sort data=&libname..&lastweek.;
by &id;
run;

data &joindata;
merge &thisweek.(in=flag1) &lastweek.(in=flag2);
by &id;
/*  full join */
%if &type=0 %then %do;
    output;
%end;
/* inner join */
%if &type=1 %then %do;
    if flag1 and flag2 then output;
%end;
/* Left Join-New Enrollment */
%if &type=2 %then %do;
    if flag1=1 and flag2=0 then do;
        keep 'Banner ID'N 'last name'n 'first name'n 'middle name'n 'Curr College'n 'Curr Level'n 'Curr Class'n 'Curr AHrs'n 'Visa Type'n;
        output;
        end;
%end;
/* Right Join- Withdraw */
%if &type=3 %then %do;
    if flag2=1 and flag1=0 then do;
        keep 'Banner ID'N 'last name'n 'first name'n 'middle name'n 'Curr College'n 'Curr Level'n 'Curr Class'n 'Curr AHrs'n 'Visa Type'n;
        output;
        end;
%end;
run;
quit;
%mend  m_sort_merge;

---

/* New enrollment */
%m_sort_merge(id='Banner ID'N,libname=work,thisweek=BIGLIST08,lastweek=BIGLIST01,joindata=newenroll,type=2);

---

/* Withdraw from School */

%m_sort_merge(id='Banner ID'N,libname=work,thisweek=BIGLIST08,lastweek=BIGLIST01,joindata=withdraw,type=3);

---

/*drop below fulltime */
%macro dropfulltime(libname=,thisweek=,lastweek=,outfile=);
proc sql;
create table drop as
select a.'Banner ID'n, a.'Last Name'n, a.'First Name'n, a.'Middle Name'n, a.'Student Inst Email Address'n,a.'Curr College'n, a.'Curr Level'n,  a.'Curr AHrs'n, b.'Curr AHrs'n as LastweekHours
from &libname..&thisweek as a
inner join &libname..&lastweek as b
on a.'Banner ID'n = b.'Banner ID'n;
quit;

data &outfile;
set work.drop;
minus = 'Curr AHrs'n - 'LastweekHours'n;
if 'minus' n < 0 then output Dropfulltime;
run;

data ug gr;
set &outfile;
if 'Curr Level'n = "UG" & 'Curr AHrs'n < 12 THEN OUTPUT UG;
if 'Curr Level'n = "GR" & 'Curr AHrs'n < 9 THEN OUTPUT GR;
run;
%mend