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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.