Hi Raju, Not sure still looking for this code. I was looking for similar logic for one of scenario . Have created a code which should suit your requirement. Have created dummy table and performed same transpose happening in header file. Only scenario code part I made a change. I have data for two key variable. My idea is to create temp array having date_key between start and end date then use 2 loops to get matching value between this two array and store it is new array. Let me know if it works in scenario.
data temp;
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;
key=2;a=1001;day=4;b=1004;amt=4000;flag='c';output;
run;
proc sort data=temp;
by key;
run;
data new;
array a (6) _temporary_;
array b (6) _temporary_;
array amt (6) _temporary_;
array flag (6) $1 _temporary_;
array day (6) _temporary_;
set temp (rename= ( a= temp1 b=temp2 amt=temp3 flag=temp4 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;
a{n} = temp1;
b{n} = temp2;
amt{n} = temp3;
flag{n} = temp4;
day{n} = temp5;
end;
if not last.key then return;
**************************;
****START*****************;
array days_count (4) _temporary_ ;
total_count=0;
do l = 1 to dim(days_count);
days_count[l]=l;
end;
*array to store total amount for each day;
array temp_amount(4) ;
* Calculate total value for each day ;
do j= 1 to 4;
do i = 1 to n;
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;
