BookmarkSubscribeRSS Feed
pradeepindia
Calcite | Level 5

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.

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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).

pradeepindia
Calcite | Level 5

dude SAS is making this complicated. That what SAS AML product is all about. Need to work under that constraints..

pradeepindia
Calcite | Level 5

guys found issue and fixed it.. now it is working.

ballardw
Super User

@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.

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
  • 4 replies
  • 739 views
  • 0 likes
  • 3 in conversation