Hi All, Guys I need one help in resolving one issue I am facing. Scenario: I have day wise multiple transaction for each key variable. I want aggregrate amt for each day per key variable and determine if agg. amt for a day is greater than 5000 or not. If so I want take count and total amt of such cases. I have developed code for same. In my sample data ,I have data for two key variable say 1 and 2. Problem: When I am executing code, for Key variable with value 1 correct total amount and days count are getting captured. However for Key variable with value 2, amt value of key variable value 1 are getting retained and getting captured for key variable with value 2. I tried first. but still it is giving incorrect value. I am not sure where I am making mistake. Restriction: I need to solve this in a one single data step and have no other option other than using arrays. it is something to do with SAS AML. we don't have option to use proc sql or create multipe data set. In my sample code below, I have added comment like ' ****User written code start*****************;' to mark starting point from where user written code starts. We cannot alter any code above that comment. below is code for reference: Sample Code: data temp (drop=a b flag); key=1;a=1001;day=1;b=1005;amt=1000;flag='c';output; key=1;a=1001;day=1;b=1005;amt=5000;flag='c';output; key=1;a=1001;day=2;b=1005;amt=6000;flag='c';output; key=1;a=1001;day=3;b=1005;amt=6000;flag='c';output; key=1;a=1001;day=4;b=1005;amt=2000;flag='c';output; key=1;a=1001;day=4;b=1004;amt=2000;flag='c';output; key=2;a=1001;day=1;b=1004;amt=4000;flag='c';output; key=2;a=1001;day=4;b=1004;amt=4000;flag='c';output; run; proc sort data=temp; by key; run; data new (drop= i j l); array amt (6) _temporary_; array day (6) _temporary_; set temp (rename= ( amt=temp3 day=temp5)); by key; if _n_ eq 1 then do; end; if first.key then n = 0; n + 1; if n le 6 then do; amt{n} = temp3; day{n} = temp5; end; if not last.key then return; ****User written code start*****************; array days_count (4) _temporary_ ; * to create array having day values from 1 to number of days for execution; total_count=0; do l = 1 to 4; days_count[l]=l; end; *array to store total amount for each day; array temp_amount(4) _temporary_; * Calculate total value for each day ; do j= 1 to 4; do i = 1 to 6; if day{i}= days_count{j} THEN temp_amount{j}=sum(temp_amount{j},amt{i}); end;end; * Identify days for which total amount is greater than limit and storing count value; do j= 1 to 4; if temp_amount[j] >5000 then do; total_count +1; total_amt_final=sum(total_amt_final,temp_amount[j]); end; end; run; Please check and share your comments.
... View more