Create a table of column totals and append daily information

[TheNovice]

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

[Reeza]

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

[TheNovice]

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

[Reeza]

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.

[TheNovice]

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

[Reeza]

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.

[TheNovice]

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.

[Reeza]

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

[TheNovice]

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

[Reeza]

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.

[TheNovice]

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?

[TheNovice]

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,

[Reeza]

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.

[TheNovice]

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

[ballardw]

---

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