Create a table of column totals and append daily information

[TheNovice]

This is how far I got...

data atb1(drop=i);
set atb; 
array testzero(*) AR_BALANCE AGE_BUCKET_0 AGE_BUCKET_1_30 AGE_BUCKET_31_60 AGE_BUCKET_61_90
age_bucket_91_120 age_bucket_121_150 age_bucket_151_plus;
do i = 1 to dim(testzero);
if testzero(i)=0 then testzero(i)=.;
end;
run;

PROC MEANS DATA=ATB1 NOPRINT ;
id date;
VAR AR_BALANCE AGE_BUCKET_0 AGE_BUCKET_1_30 AGE_BUCKET_31_60 AGE_BUCKET_61_90
age_bucket_91_120 age_bucket_121_150 age_bucket_151_plus;
OUTPUT OUT=ATB2 (drop= _TYPE_ _FREQ_) SUM= N= / autoname ;
RUN;

PROC TRANSPOSE DATA=ATB2 OUT = ATB3
prefix = date;
RUN;

The output i get is this: 

_NAME_	_LABEL_	date1
AR_BALANCE_Sum	AR_BALANCE	4,517,795,437
AGE_BUCKET_0_Sum	AGE_BUCKET_0	575,298,949
AGE_BUCKET_1_30_Sum	AGE_BUCKET_1_30	190,827,228
AGE_BUCKET_31_60_Sum	AGE_BUCKET_31_60	58,694,576
AGE_BUCKET_61_90_Sum	AGE_BUCKET_61_90	32,294,689
AGE_BUCKET_91_120_Sum	AGE_BUCKET_91_120	29,674,161
AGE_BUCKET_121_150_Sum	AGE_BUCKET_121_150	25,597,435
AGE_BUCKET_151_PLUS_Sum	AGE_BUCKET_151_PLUS	3,674,579,799
AR_BALANCE_N	AR_BALANCE	8,610,964
AGE_BUCKET_0_N	AGE_BUCKET_0	3,445,686
AGE_BUCKET_1_30_N	AGE_BUCKET_1_30	1,188,699
AGE_BUCKET_31_60_N	AGE_BUCKET_31_60	396,280
AGE_BUCKET_61_90_N	AGE_BUCKET_61_90	180,639
AGE_BUCKET_91_120_N	AGE_BUCKET_91_120	142,042
AGE_BUCKET_121_150_N	AGE_BUCKET_121_150	130,128
AGE_BUCKET_151_PLUS_N	AGE_BUCKET_151_PLUS	4,366,092

I don't know how to proceed further. I can't figure out how to use Proc tabulate so I can keep appending the 3rd column every day to this data. The column should be dated...

[Reeza]

Figure it out for a single month first. Add in the date to that using a data and then use PROC APPEND to append the results. Then you can use PROC REPORT or TABULATE to flip it.

[ScottBass]

Why is it so hard to get noobs to post their question in the form of a self-contained have/want data step?  Why do you expect us to convert your sample data into working code?

(Forum admins, when someone creates a new account, can you send them an email, or at least redirect them to a link, that directs them how to ask a question?)

Anyway, after expending the effort in converting the cut-and-paste job from your original post into a working data step, this is what I've got so far.  It's incomplete, but since you haven't been clear in asking your question, perhaps you can run with this and finish it.

You need to add loaddate to your data - the code can't magically know what data belongs to what loaddate.

My questions:

* How do you derive count?

* Does ID have any relevance to what you're doing?

* How do you want to name your columns?  Do they have to be SAS V7 column name compliant, or are you happy with name literals and all that entails?  See http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#p18cdcs4v5wd2dn1q0x2...

data have;
input ID : 4. bucket1-bucket3 : 5.2 loaddate : date9.;
format bucket: 5.2 loaddate date9.;
datalines;
1234   10.00   20.00   70.00   01JAN2019
3456   10.00   20.00   70.00   01JAN2019
1234   10.00   20.00   70.00   02JAN2019
3456   10.00   20.00   70.00   02JAN2019
1234   10.00   20.00   70.00   03JAN2019
3456   10.00   20.00   70.00   03JAN2019
;
run;

data dates;
   do loaddate="01JAN2019"d to "31DEC2019"d;
      output;
   end;
   format loaddate date9.;
run;

data source;
   merge have dates;
   by loaddate;
run;

proc summary data=source missing;
   by loaddate;
   var bucket:;
   output out=totals (drop=_type_ _freq_) sum= n= / autoname;
run;

options validvarname=any;

* I don't know why the output does not have the loaddate (eg. 01JAN2019) as the column label ;
* I'm running SAS 9.3 and EG 7.x;
* Perhaps it will work properly for you??? ;
proc transpose data=totals out=sums;
   idlabel loaddate;
run;

data want;
   set sums;
   array totals{*} COL:;
   totals_bucket=0;
   do i=1 to dim(totals);
      label=vlabel(totals{i});
      putlog label=;
      * bail on first missing column ;
      if missing(totals{i}) then leave;
      totals_bucket+totals{i};
      totals{i}=totals_bucket;
   end;
   drop totals: i;
run;

* Compare with the above output ;
proc transpose data=totals out=sums2;
   id loaddate;
run;

data want2;
   set sums2;
   array totals{*} '01JAN2019'n--'31DEC2019'n;
   totals_bucket=0;
   do i=1 to dim(totals);
      label=vlabel(totals{i});
      putlog label=;
      * bail on first missing column ;
      if missing(totals{i}) then leave;
      totals_bucket+totals{i};
      totals{i}=totals_bucket;
   end;
   drop totals: i;
run;

[TheNovice]

Thank you Scott,

This noob doesn't know much. I have been teaching SAS myself and it's been a bit tough. That being said, I appreciate your bluntness and direction. Moving forward, I will post as you suggested. I haven't before but no one has said otherwise. 

I will look at your response today and try to work it out. 

Thanks again

[Reeza]

Here's a fully worked example, which assumes you import the data every day correctly already into a data set called have. Then every day you would call the %addData macro and %createReport macro to generate your report.

You should probably verify a few things:

1. Path to where you'll store the reportingData set permanently

2. You should add some protection so reportingData cannot easily be accidentally deleted

3. You should add some verification to ensure you don't load the same data twice, ie if something errors out you may have to remove that dates record first and then add it back in. 

4. Error checking for parameters in the macro to exist.

But this should get you started and going. The code is somewhat commented so please read the comments to help you understand what each step is doing.

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);

[ScottBass]

Hi @Reeza, I haven't looked too closely at the PROC TABULATE code (or revisited the docs - it's been a while since I've used it).  However, does your code calculate the running daily sums as the OP desired?  Or perhaps I misunderstood what the OP was asking?

[Reeza]

No, I didn't catch that. However, that could be added as a data step instead of proc tabulate. The important part for this question, IMO, is the design of the program and how it should be structured.