topic Re: Create a table of column totals and append daily information in SAS Programming

Create a table of column totals and append daily information

TheNovice — Thu, 13 Jun 2019 20:04:40 GMT

Hi all,

I posted this earlier but not sure what happened with the message. I have data in the following format which is run daily

ID	balance	bucket1	bucket2	bucket3
1234	100.00	10.00	20.00	70.00
3456	100.00	10.00	20.00	70.00

I need to create a worksheet that tracks the balances and count by column daily. SO output should be like. I know how to do it tomorrow using proc sql and sum function but is there a more efficient and faster method?

	01-Jan	02-Jan
balance	sum	sum
bucket1	sum	sum
bucket2	sum	sum
bucket3	sum	sum

	01-Jan	02-Jan
balance	count	count
bucket1	count	count
bucket2	count	count
bucket3	count	count

Re: Create a table of column totals and append daily information

Reeza — Thu, 13 Jun 2019 20:08:54 GMT

Where do the dates come from? How are the values calculated?

Re: Create a table of column totals and append daily information

TheNovice — Thu, 13 Jun 2019 20:35:22 GMT

Hi Reeza,

The file is run daily so the dates denote the daily run. I am trying to build out an aggregated view where the new totals for the day would just be added as a column so i have a daily summary

Re: Create a table of column totals and append daily information

Reeza — Thu, 13 Jun 2019 20:39:41 GMT

I guess I'm not seeing a way I can know, and therefore tell the computer/program, how that data should be organized. It's quite easy to get reports in that format but how depends on the data structure. What you've shown is too generic, so the best answer right now is look into PROC REPORT and across variables. Or provide more details about your problem.

Re: Create a table of column totals and append daily information

TheNovice — Thu, 13 Jun 2019 21:54:25 GMT

So, if i get the following file on Jan 1

ID	balance	bucket1	bucket2	bucket3
1234	100.00	10.00	20.00	70.00
3456	100.00	10.00	20.00	70.00

then I need 2 summaries like:

	01-Jan	02-Jan
balance	200
bucket1	20
bucket2	40
bucket3	140

	01-Jan	02-Jan
balance	2
bucket1	2
bucket2	2
bucket3	2

Then on Jan 2 I would run the same report and attach to above. So on and so forth...

Re: Create a table of column totals and append daily information

Reeza — Thu, 13 Jun 2019 21:59:24 GMT

Bad practice, IMO. Take your main data above, insert into a table that has full data and then run your report on the full data again. I suppose you could run summaries each day, but again that structure isn't recommended, that's for reporting. So it would still be a long file and then you'd report of it.

Re: Create a table of column totals and append daily information

TheNovice — Thu, 13 Jun 2019 22:18:07 GMT

Thank you Reeza,

I have no choice. The table is refreshed daily and we need a summary of the daily snapshot.

But I don't know how to best proceed.

Right now, I would do the following:

Proc sql;

create table have as

select sum(balance),sum(bucket1),sum(bucket2)

from table a

;

quit

and then proc transpose the above to get it in the format I want but I feel like there is a better way. I just don't know it and can't seem to figure it out.

Re: Create a table of column totals and append daily information

Reeza — Thu, 13 Jun 2019 22:20:39 GMT

Is your final format required a table or report?
Have you tried basic PROC MEANS?

Re: Create a table of column totals and append daily information

TheNovice — Thu, 13 Jun 2019 22:25:27 GMT

A report tracking the totals by day.. The idea is to automate it so the code runs everyday and attaches that day's total to the report

I have not tried proc means, will try it right now. It's something i haven't used before

Re: Create a table of column totals and append daily information

Reeza — Thu, 13 Jun 2019 22:31:24 GMT

By report I mean a PDF, Excel or HTML document that gets emailed to someone. If that's not the case, proc means and append is all you likely need so your approach sounds right. You're not getting a whole lot of improvement by switching anything. The only room for improvement would be to switch it to a structure I've mentioned previously where the report is generated every day from scratch and you only maintain the raw data.

Re: Create a table of column totals and append daily information

TheNovice — Thu, 13 Jun 2019 22:59:11 GMT

It would be Excel

i tried proc means but I can't figure it out. this is how i am doing it:

Proc sql;
create table Test as
select
sum(AR_BALANCE) as AR_BALANCE,
SUM(BUCKET_0) AS BUCKET_0,
SUM(BUCKET_1) AS BUCKET_1,
SUM(BUCKET_2) AS BUCKET_2,
SUM(BUCKET_3) AS BUCKET_3,
from atb.atb
;
QUIT;

PROC TRANSPOSE DATA=TEST OUT=TEST1;
RUN;

I would just have to run this everyday and then do a left join to the existing report?

Re: Create a table of column totals and append daily information

TheNovice — Thu, 13 Jun 2019 23:37:27 GMT

Also,

How would I rename the date1 variable to show as the current date ?Is this possible? I would just keep running the code and joining to this output every day....

_NAME_ date1
AR_BALANCE 4555947291.7
BUCKET_0 627673027.55
BUCKET_1 182100256.65
BUCKET_2 55872964.12
BUCKET_3 32185457.8

Thanks,

Re: Create a table of column totals and append daily information

Reeza — Fri, 14 Jun 2019 02:10:07 GMT

No, if it's going to Excel then use a report.

1. do a proc means on the data
https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

2. APPEND the results with the date to your summary table (PROC APPEND).

3. Use PROC TABULATE or PROC REPORT to create the report and use ODS excel to export it straight to an Excel file

4. If you're getting bored, learn how to have it sent of f via email at the end of the job automatically.

If you're willing to restructure your process, you take the input data, append it to the main data set (proc append), then run proc tabulate straight to excel and email. Saves you two steps or so and usually a lot of error checking.

Re: Create a table of column totals and append daily information

TheNovice — Fri, 14 Jun 2019 19:12:35 GMT

Thank you, I am on track... is there a way to count with Proc means ? or should i just proc sql for that ?

Re: Create a table of column totals and append daily information

ballardw — Fri, 14 Jun 2019 20:08:45 GMT

@TheNovice wrote:

Thank you, I am on track... is there a way to count with Proc means ? or should i just proc sql for that ?

Count what?

The N statistic returns the number of records of a variable that are not missing , which would generally "count" the number of something, The NMISS statistic "counts" the number of missing values of a variable.

Re: Create a table of column totals and append daily information

TheNovice — Tue, 18 Jun 2019 01:32:33 GMT

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...

Re: Create a table of column totals and append daily information

Reeza — Tue, 18 Jun 2019 04:20:57 GMT

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.

Re: Create a table of column totals and append daily information

ScottBass — Tue, 18 Jun 2019 04:40:36 GMT

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#p18cdcs4v5wd2dn1q0x296d3qek6.htm

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;

Re: Create a table of column totals and append daily information

TheNovice — Tue, 18 Jun 2019 15:19:20 GMT

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

Re: Create a table of column totals and append daily information

Reeza — Tue, 18 Jun 2019 16:14:29 GMT

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