SAS EG - dating my data

[Margot89]

Hi Everyone,

I would like to include a column in my table that states the date new data was added to the table. In the current situation, I have to save the data in an Excel file (that get's pulled from SAP) on the company server and add to it when a new month starts (and new financial data becomes available). I rerun the project in SAS EG and the data uploads. I would like to 'mark' every new entry (row) in the table with today's date. Of course, last month's data should keep last month's date. Is this at all possible? Since I am adding to the same excel file I don't know how to keep the dates separated. I don't want to update all the dates every month, only add new dates to new entries. Your help is much appreciated!

[Reeza]

Here's an example of how you do that. Look at the line where I calculate loadDate and add that in to my data before I append it to the main data set. As indicated in my comments as well, ideally I should also be checking if data for that day has already been loaded to ensure it doesn't load data twice, if there's an error and you run the code twice.

/*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=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=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);