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.
I am afraid this: "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." is nonsense. You should use the appropriate code to perform the task. You already have two steps in that code provided, a sort and a datastep. As for your problem, I don't see why you need arrays, just retain some overall count variables? It seems to me to be a case of over complicating things, and imposing artificial restrictions is the cause of the problems here.
Try presenting the problem as a have (data) and a want (output data).
dude SAS is making this complicated. That what SAS AML product is all about. Need to work under that constraints..
guys found issue and fixed it.. now it is working.
@pradeepindia wrote:
guys found issue and fixed it.. now it is working.
Please post what the issue was, how you fixed it and then mark that as the accepted solution.
That way anyone searching this forum for help with a similar topic knows what the solution is if they have a similar problem.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: