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