*This code was developed for the 2022 SAS NCAIR Summer Drive-In
**On July 16, 2022, on the SAS Campus in Cary, NC. 
***By Andrew Walker, www.walkerdataranger.com
****aew5044@gmail.com or aewalke3@ncsu.edu;


*******************Level I Macros************************;

*Hardcoded macro;
%LET Name = Andrew Walker;


*Multiple hard-coded macros;
%LET ReportingTerm=2022;
%LET Past1Term = 2021;
%LET Past2Term = 2020;
%LET Past3Term = 2019;

*Reduce redundancy and the opportunity for data entry error;
*by using &SYSFUNC and %EVAL;
%LET ReportingTerm=2022;

%LET Past1Term = %EVAL(&ReportingTerm -1);
%LET Past2Term = %EVAL(&ReportingTerm -2);
%LET Past3Term = %EVAL(&ReportingTerm -3);

*Use %SYSFUNC to call traditional SAS functions;
%LET ReportingTerm=2020FA;
%LET PastTerm = %EVAL(%SYSFUNC(SUBSTR(&ReportingTerm,1,4))-1)FA;

*Automate the entire process to reduce the likelihood of data entry error;

%MACRO Past;
%LET ReportingTerm=%EVAL(%SYSFUNC(YEAR(%SYSFUNC(Today())))-2);
	%DO a=1 %to 6;
	%GLOBAL PastFATerm&a PastSPTerm&a PastSU1Term&a PastSU2Term&a;
		%LET PastFATerm&a=%SYSEVALF(&ReportingTerm-&a.)FA;
		%LET PastSPTerm&a=%SYSEVALF(&ReportingTerm-&a.)SP;
		%LET PastSU1Term&a=%SYSEVALF(&ReportingTerm-&a.)SU1;
		%LET PastSU2Term&a=%SYSEVALF(&ReportingTerm-&a.)SU2;
	%END;
%MEND Past;

%Past;

***************Review what was just made;

proc sql;
	Create Table Macro_List As
	Select name, value
	from dictionary.macros
            	      where scope='GLOBAL' 
			   and FIND(name,'SYS_SQL_IP_')=0 and 
				FIND(name,'SQL')=0  and 
				FIND(name,'SAS')=0  and 
				FIND(name,'SYS')=0  and 
				SUBSTR(name,1,1) NE '_';
   	quit;


*Now I want to create macros based on values in a data set;
*We Need some data;

Data Terms;
Infile Datalines delimiter=',';
Input Acad_Term :$7. Acad_year :$7.;
Datalines;
2018FA,2018-19
2019SP,2018-19
2019SU1,2018-19
2019SU2,2018-19
;

*This code will only capture the first value;
PROC SQL Noprint;
Select Distinct Acad_Term
Into :CUTerm
From Terms;
Quit;

%PUT &CUTERM;

*Lets make a long list of values and define a macro called CUTerm;

PROC SQL Noprint;
Select Distinct Acad_Term
Into: CUTerm Separated by ", "
From Terms;
Quit;

%PUT &CUTERM;

*Now, let's create to lists in one SQL statement;

PROC SQL Noprint;
Select Distinct QUOTE(COMPRESS(Acad_Term)) as Acad_Term,
Count(Distinct Acad_year) as Distinct_Acad_Year
Into :CUTerm Separated by ", ", :Dist
From terms;
Quit;

%PUT &CUTERM &Dist;

*********************What are all the macros created thus far?;
proc sql ;
	Select name, value
	INTO :Name separated by ', ', :Val separated by ', '
	from dictionary.macros
            	      where scope='GLOBAL' 
			   and not name contains 'SYS_SQL_IP_' and 
				FIND(name,'SQL')=0  and 
				FIND(name,'SAS')=0  and 
				FIND(name,'SYS')=0  and 
				SUBSTR(name,1,1) NE '_';
   	quit;


*******************Level II Macros************************;
*This is a short section becuase it so straigt forward.  
First we are going to create a macro called Import;

%Macro Import(Y=);
libname Ex&Y. xlsx "S:\My Excel File-&y..xlsx";

Data SCH&Y label&y;
	set ex&Y.."&Y data$"n;
	TwoDigit = SCAN(ciporig ,1,'.');
	Year=&Y.;
Run;

libname ex&y clear;

%MEND Import;

*Then we are going to call the macro for each reporting year.;

%Import(Y=2016);
%Import(Y=2017);
%Import(Y=2018);
%Import(Y=2019);
%Import(Y=2020);
%Import(Y=2021);
%Import(Y=2022);

*Step 1: create your model;

libname Ex2016 xlsx "S:\My Excel File-2016.xlsx";

Data SCH2016;
	set ex2016."2016 data$"n;
	Year=2016;
Run;

libname ex2016 clear;

*Step 2 wrap it in %MACRO and %MEND and replace all the changing variables with your macro variable;
*In this case it is &Y;

%MACRO IMPORT(Y=);
libname Ex&Y. xlsx "S:\My Excel File-&Y..xlsx";

Data SCH&Y;
	set ex&Y."&Y data$"n;
	Year=&Y;
Run;

libname ex&Y clear;

%MEND IMPORT;

*****Level III Fun with the log;
*****This will put an error on your log if you run it outside the months of JAN, FEB, MAR, APR;

options minoperator;
%MACRO Z;
%IF %SYSFUNC(MONTH(%SYSFUNC(today()))) in (1 2 3 4 7) %THEN %DO; 
%Global T Term TermFY1;
	%LET T=%EVAL(%SYSFUNC(YEAR(%SYSFUNC(today())))-2); 
	%LET term=&T.6;
	%LET termfy1 = &t.6;
%END;
%ELSE	%Do; %PUT ERROR: You are running this report in the wrong month!;
		%PUT ERROR: We run this report between January and April.; %END;
%MEND Z;

%Z;

*****************Creating If Statements****************;
Data Datesz(Drop=i);
	Do i=1990 to 2049;
		Year1_IPEDS=CATS("'",'30JUN',PUT(i,8.),"'",'d'); 
		Year2_IPEDS=CATS("'",'01JUL',PUT((i+1),8.),"'",'d'); 
		Year1_Acad=CATS("'",'01SEP',PUT(i,8.),"'",'d'); 
		Year2_Acad=CATS("'",'31AUG',PUT((i+1),8.),"'",'d'); 
		Year1_Cal=CATS("'",'01JAN',PUT(i,8.),"'",'d'); 
		Year2_Cal=CATS("'",'31DEC',PUT((i+1),8.),"'",'d'); 
		Completion_Term = CATS("'",
		SUBSTR(COMPRESS(PUT(i,8.)),1,1),
		SUBSTR(COMPRESS(PUT(i,8.)),3,2),
			'6',
			",",
		SUBSTR(COMPRESS(PUT(i,8.)),1,1),
		SUBSTR(COMPRESS(PUT(i,8.)),3,2),
			'7',
			",",
		SUBSTR(COMPRESS(PUT(i,8.)),1,1),
		SUBSTR(COMPRESS(PUT(i,8.)),3,2),
			'8',
			",",
		SUBSTR(COMPRESS(PUT(i,8.)),1,1),
		SUBSTR(COMPRESS(PUT((i+1),8.)),3,2),
			'1',
			"'");
		cal_year2 = COMPRESS(PUT(i,8.));
		Yearz = CATS(PUT(i,8.),'-',SUBSTR(COMPRESS(PUT((i+1),8.)),3,2));


		Output; 
End; 
Run;

Data _NULL_;
	set Datesz End=NoMore;
	Call SYMPUTX(catt("Year1_IPEDS",_N_),Year1_IPEDS);
	Call SYMPUTX(catt("Year2_IPEDS",_N_),Year2_IPEDS);
	Call SYMPUTX(catt("Year1_Acad",_N_),Year1_Acad);
	Call SYMPUTX(catt("Year2_Acad",_N_),Year2_Acad);
	Call SYMPUTX(catt("Year1_Cal",_N_),Year1_Cal);
	Call SYMPUTX(catt("Year2_Cal",_N_),Year2_Cal);
	Call SYMPUTX(catt("Terms",_N_),Completion_Term);
	Call SYMPUTX(catt("Yearz",_N_),yearz);
	Call SYMPUTX(catt("cal_yeara",_N_),cal_year2);
	If NoMore then Call SYMPUTX('Numrows',_N_);
Run;

*Auto locates with the SAS EPG Project lives;
%LET b = %SYSFUNC(SUBSTR(%SYSFUNC(dequote(&_CLIENTPROJECTPATH)),1,
			%EVAL((%SYSFUNC(FIND((%SYSFUNC(deQUOTE(&_CLIENTPROJECTPATH))),
			%SYSFUNC(deQUOTE(&_CLIENTPROJECTNAME)))))-2)));

%PUT &B;

DATA _NULL_;
 FILE "&b.Parameters &TERM %Sysfunc(Today(),YYMMDD10.).txt";
PUT
@1 'term = ' "&Term" /
@1 'termfy1 = ' "&termfy1" /
@1 'termfy2 = ' "&termfy2" /
@1 'FYMin = ' "&FYMin" /
@1 'FYMax = ' "&FYMax" /
@1 'Yr3Min = ' "&Yr3Min" /
@1 'Yr3Max = ' "&Yr3Max";
Run;