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 |
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
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...
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.
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
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?
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,
Thank you, I am on track... is there a way to count with Proc means ? or should i just proc sql for that ?
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.