SAS - Case Study - How to add data to a regular report and then report on it daily

Example_adding_data_reporting_process

/*This program illustrates how to build a daily process that will add in your data and then create an automatic report every day

Author: F. Khurshed
Date: 2019-06-18
*/

data have;
	input ID : 4. bucket1-bucket3 : 5.2;
	format bucket: 5.2;
	datalines;
1234   10.00   20.00   70.00 
3456   10.00   20.00   70.00
1234   10.00   20.00   70.00
3456   10.00   20.00   70.00
1234   10.00   20.00   70.00
3456   10.00   20.00   70.00
;
run;

%macro addData(dsin=);
	*should add a check to ensure data has not been loaded already;
	*calculate summary statistics;
	ods select none;

	proc means data=&dsin stackods n sum;
		class ID;
		var bucket:;
		ods output summary=_stats;
	run;

	ods select all;
	**add in load date;

	data _stats2;
		set _stats;
		loadDate=today();
		format loadDate date9.;
		rename N=Count SUM=TOtal;
		keep ID Variable N SUM loadDate;
	run;

	*add data to master data set each day;
	*this should go to a permanent library;
	libname myData '/home/fkhurshed/Demo1';

	proc append base=myData.reportingData data=_stats2 force;
	run;

	libname myData;
	*removes temporary tables, uncomment once you are sure how it works;
	*proc sql noprint;
	*drop table _stats _stats2;
	*quit;
%mend;

%macro createReport(path=);
	*creates Excel file with data;
	ods excel file="&path." style=meadow;
	*report data for counts data;
	title "Report for %sysfunc(today(), worddate.)";
	title2 "Counts";

	proc tabulate data=myData.reportingData;
		class loadDate Variable;
		var Count;
		table Variable='', loadDate=''*Count=''*n='#'*f=8.0;
	run;

	*report data for sum data;
	title1;
	title2 "Sums";

	proc tabulate data=myData.reportingData;
		class loadDate Variable;
		var TOtal;
		table Variable='', loadDate=''*Total=''*sum='Total $'*f=8.0;
	run;

	ods excel close;
%mend;

%addData(dsin=have);
%createReport(path=/home/fkhurshed/Demo.xlsx);