BookmarkSubscribeRSS Feed
RK
Calcite | Level 5 RK
Calcite | Level 5

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

5 REPLIES 5
ballardw
Super User

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
Calcite | Level 5 RK
Calcite | Level 5

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
Opal | Level 21

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;
PG
RK
Calcite | Level 5 RK
Calcite | Level 5
This is just awesome. Thanks so much!
PGStats
Opal | Level 21

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

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 755 views
  • 1 like
  • 3 in conversation