acctno datetime amount counts_cumulative txn_counts amount_cumulative
999999 05JUN2015:10:27:25.064000 875 0 0 0
999999 05JUN2015:10:27:54.399000 1800 1 1 875
999999 20JUN2015:11:17:34.064000 1500 0 0 0
999999 20JUN2015:11:18:10.588000 750 1 1 1500
9999999 19JUN2015:16:36:22.581000 146.85 0 0 0
9999999 19JUN2015:16:36:22.581000 28.37 0 0 0
9999999 19JUN2015:16:36:22.581000 37.87 0 0 0
9999999 19JUN2015:16:36:22.581000 25 0 0 0
9999999 19JUN2015:16:36:22.581000 100 0 0 0
9999999 19JUN2015:16:36:22.581000 50.47 0 0 0
9999999 19JUN2015:17:43:24.528000 100 6 1 388.56
9999999 19JUN2015:17:43:24.528000 100 6 1 388.56
9999999 19JUN2015:17:43:24.528000 100 6 1 388.56
9999999 19JUN2015:17:43:24.528000 100 6 1 388.56
9999999 19JUN2015:17:43:24.528000 100 6 1 388.56
9999999 19JUN2015:17:43:24.528000 100 6 1 388.56
99999999 20JUN2015:17:43:24:528000 50.47 0 0 0
99999999 20JUN2015:17:43:24:528000 25.85 0 0 0
99999999 20JUN2015:17:43:24:528000 28.37 0 0 0
99999999 20JUN2015:17:43:24:528000 34.89 0 0 0
99999999 20JUN2015:17:43:24:528000 74.36 0 0 0
What is your expected output?
Do you want the columns in your dataset in a different order? Or do you need to have them appear in a different order in a report (printed or document)?
Please show what you expect the output to look like.
And have you actually read this into a SAS dataset?
Hi,
The input columns in a dataset are below
acctno | datetime | amount |
999999 | 05JUN2015:10:27:25.064000 | 875 |
999999 | 05JUN2015:10:27:54.399000 | 1800 |
999999 | 20JUN2015:11:17:34.064000 | 1500 |
999999 | 20JUN2015:11:18:10.588000 | 750 |
9999999 | 19JUN2015:16:36:22.581000 | 146.85 |
9999999 | 19JUN2015:16:36:22.581000 | 28.37 |
9999999 | 19JUN2015:16:36:22.581000 | 37.87 |
9999999 | 19JUN2015:16:36:22.581000 | 25 |
9999999 | 19JUN2015:16:36:22.581000 | 100 |
9999999 | 19JUN2015:16:36:22.581000 | 50.47 |
9999999 | 19JUN2015:17:43:24.528000 | 100 |
9999999 | 19JUN2015:17:43:24.528000 | 100 |
9999999 | 19JUN2015:17:43:24.528000 | 100 |
9999999 | 19JUN2015:17:43:24.528000 | 100 |
9999999 | 19JUN2015:17:43:24.528000 | 100 |
9999999 | 19JUN2015:17:43:24.528000 | 100 |
99999999 | 20JUN2015:17:43:24:528000 | 50.47 |
99999999 | 20JUN2015:17:43:24:528000 | 25.85 |
99999999 | 20JUN2015:17:43:24:528000 | 28.37 |
99999999 | 20JUN2015:17:43:24:528000 | 34.89 |
99999999 | 20JUN2015:17:43:24:528000 | 74.36 |
and I am expecting the output dataset as like below (with additional three variables with cumulative counts and amount created)
acctno | datetime | amount | counts_cumulative | txn_counts | amount_cumulative |
999999 | 05JUN2015:10:27:25.064000 | 875 | 0 | 0 | 0 |
999999 | 05JUN2015:10:27:54.399000 | 1800 | 1 | 1 | 875 |
999999 | 20JUN2015:11:17:34.064000 | 1500 | 0 | 0 | 0 |
999999 | 20JUN2015:11:18:10.588000 | 750 | 1 | 1 | 1500 |
9999999 | 19JUN2015:16:36:22.581000 | 146.85 | 0 | 0 | 0 |
9999999 | 19JUN2015:16:36:22.581000 | 28.37 | 0 | 0 | 0 |
9999999 | 19JUN2015:16:36:22.581000 | 37.87 | 0 | 0 | 0 |
9999999 | 19JUN2015:16:36:22.581000 | 25 | 0 | 0 | 0 |
9999999 | 19JUN2015:16:36:22.581000 | 100 | 0 | 0 | 0 |
9999999 | 19JUN2015:16:36:22.581000 | 50.47 | 0 | 0 | 0 |
9999999 | 19JUN2015:17:43:24.528000 | 100 | 6 | 1 | 388.56 |
9999999 | 19JUN2015:17:43:24.528000 | 100 | 6 | 1 | 388.56 |
9999999 | 19JUN2015:17:43:24.528000 | 100 | 6 | 1 | 388.56 |
9999999 | 19JUN2015:17:43:24.528000 | 100 | 6 | 1 | 388.56 |
9999999 | 19JUN2015:17:43:24.528000 | 100 | 6 | 1 | 388.56 |
9999999 | 19JUN2015:17:43:24.528000 | 100 | 6 | 1 | 388.56 |
99999999 | 20JUN2015:17:43:24:528000 | 50.47 | 0 | 0 | 0 |
99999999 | 20JUN2015:17:43:24:528000 | 25.85 | 0 | 0 | 0 |
99999999 | 20JUN2015:17:43:24:528000 | 28.37 | 0 | 0 | 0 |
99999999 | 20JUN2015:17:43:24:528000 | 34.89 | 0 | 0 | 0 |
99999999 | 20JUN2015:17:43:24:528000 | 74.36 | 0 | 0 | 0 |
First separate the date and time with a data view and process with nested DOW loops
data temp / view=temp;
set have;
date = datepart(datetime);
time = timepart(datetime);
format date date9. time time8.;
run;
data want;
length acctno datetime amount 8;
do until (last.accno);
counts_cumulative = 0;
txn_counts = 0;
amount_cumulative = 0;
do until(last.date);
time_rec_count = 0;
time_sum = 0;
do until(last.time);
set temp; by acctno date time;
output;
time_rec_count + 1;
time_sum + amount;
end;
txn_counts + 1;
counts_cumulative + time_rec_count;
amount_cumulative + time_sum;
end;
end;
drop time_rec_count time_sum date time;
run;
proc print data=want noobs; run;
You are welcome. Please run the code on your full dataset before you mark the solution as accepted.
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.