Help: First three columns as an input have to output last three columns.

[RK]

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

[ballardw]

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?

[RK]

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

[PGStats]

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;

[RK]

This is just awesome. Thanks so much!

[PGStats]

You are welcome. Please run the code on your full dataset before you mark the solution as accepted.