DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor RK
Occasional Contributor
Posts: 7

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

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

Super User
Posts: 11,343

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

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?

Occasional Contributor RK
Occasional Contributor
Posts: 7

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

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
Respected Advisor
Posts: 4,919

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

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
Occasional Contributor RK
Occasional Contributor
Posts: 7

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

This is just awesome. Thanks so much!
Respected Advisor
Posts: 4,919

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

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

PG
Ask a Question
Discussion stats
  • 5 replies
  • 200 views
  • 1 like
  • 3 in conversation