topic Re: Converting a column into an Array for nested do loops in SAS Programming

Converting a column into an Array for nested do loops

Kirotheninja — Sun, 21 Jul 2019 04:29:24 GMT

data cash_flow;
input account_num 
	  account_desc $
	  trans_key
	  amount
	  tran_status $
	  ;
cards;
1 P 23 200 SUCCESS
2 P 45 205 SUCCESS
3 P 22 409 FAILURE
4 P 34 343 SUCCESS
1 P 23 343 SUCCESS
1 P 45 506 SUCCESS
;
RUN;
/* --------------------------------------------------------------------
Creating a table with required parameters
   -------------------------------------------------------------------- */
PROC SQl;
create table dat1 as
select * from cash_flow
where account_desc='P' and tran_status='SUCCESS' and account_num=1;
quit;
proc sql;
create table want as 
select amount from dat1;
data arraysss;
set want;
array try[*] _numeric_;
sum1=0;
do i=1 to dim(try);
	c=0;sum2=0;
		do j=1 to dim(try);
		put try[i]=;
		put try[j]=;
			a=abs((try[i]-try[j])/try[i])*100;
			if a<5 then sum2=sum2+try[j];
			put a=;
		c=sum2;
	if c>sum1 then sum1=c;

	end;
	end;
	drop i j;
threshold=100;
if sum1>threshold then alert=1;
run;

Can you tell me where i'm going wrong. If i write the values into the array manually, the loop seems to work just fine. but when i'm needed for the array to be as the data provided in the above table i haven't been able to 😕

Re: Converting a column into an Array for nested do loops

KachiM — Sun, 21 Jul 2019 05:14:07 GMT

It will be easy to check the code if you can show the expected output.

Re: Converting a column into an Array for nested do loops

Kurt_Bremser — Sun, 21 Jul 2019 05:59:08 GMT

Table want has only one column, so the array will have a size of one and the loops only one iteration.

Re: Converting a column into an Array for nested do loops

Tom — Sun, 21 Jul 2019 15:28:18 GMT

Please explain what you are trying to do.

Since your dataset WANT only has one variable the ARRAY will only have one variable in it. So there is nothing to loop over.

So on the first observation the variable AMOUNT has the value 200.

The array has a dimension of 1.

So your code set these values:

sum1=0
c=0
sum2=0
a=0
sum2=200
c=200
sum1=200
threshold=100
alert=1

So at the end the first iteration of the data step the values will be.

amount=200 sum1=200 c=200 sum2=200 a=0 threshol=100 alert=1

So that observation is written and the data step will now process the next observation.

Re: Converting a column into an Array for nested do loops

Kirotheninja — Sun, 21 Jul 2019 16:37:16 GMT

data alert;
ARRAY temp[7] (200 203 205 206 208 250 300);
put _all_;
/*Running nested do loops over the array*/
sum1=0;
do i=1 to dim(temp);
	c=0;sum2=0;
		do j=1 to dim(temp);
		put temp[i]=;
		put temp[j]=;
			a=abs((temp[i]-temp[j])/temp[i])*100;
			if a<5 then sum2=sum2+temp[j];
			put a=;
		c=sum2;
	if c>sum1 then sum1=c;
	put sum2=;
	put sum1=;
	end;
	end;
	drop i j  sum2 a c temp1-temp7;
/*Check with the threshold for AGP*/
threshold=300;
if sum1>threshold then alert="Generated" ; else alert="Not Generated";
run;
proc print;
run;

this runs perfectly as intended. please check

Re: Converting a column into an Array for nested do loops

Kirotheninja — Sun, 21 Jul 2019 16:38:12 GMT

yeah but i want to run a nested loop. like in this code shown below.

data alert;
ARRAY temp[7] (200 203 205 206 208 250 300);
put _all_;
/*Running nested do loops over the array*/
sum1=0;
do i=1 to dim(temp);
	c=0;sum2=0;
		do j=1 to dim(temp);
		put temp[i]=;
		put temp[j]=;
			a=abs((temp[i]-temp[j])/temp[i])*100;
			if a<5 then sum2=sum2+temp[j];
			put a=;
		c=sum2;
	if c>sum1 then sum1=c;
	put sum2=;
	put sum1=;
	end;
	end;
	drop i j  sum2 a c temp1-temp7;
/*Check with the threshold for AGP*/
threshold=300;
if sum1>threshold then alert="Generated" ; else alert="Not Generated";
run;
proc print;
run;

Re: Converting a column into an Array for nested do loops

Kirotheninja — Sun, 21 Jul 2019 16:39:46 GMT

data alert;
ARRAY temp[7] (200 203 205 206 208 250 300);
put _all_;
/*Running nested do loops over the array*/
sum1=0;
do i=1 to dim(temp);
	c=0;sum2=0;
		do j=1 to dim(temp);
		put temp[i]=;
		put temp[j]=;
			a=abs((temp[i]-temp[j])/temp[i])*100;
			if a<5 then sum2=sum2+temp[j];
			put a=;
		c=sum2;
	if c>sum1 then sum1=c;
	put sum2=;
	put sum1=;
	end;
	end;
	drop i j  sum2 a c temp1-temp7;
/*Check with the threshold for AGP*/
threshold=300;
if sum1>threshold then alert="Generated" ; else alert="Not Generated";
run;
proc print;
run;

This is my intention. But here i'm having to put in the values into an array manually. is tthere any way i can reference these values automatically?

Re: Converting a column into an Array for nested do loops

Tom — Sun, 21 Jul 2019 16:50:43 GMT

Explain what you are trying to do and perhaps someone can translate into code that can work on dataset instead of just a single list of numbers.

Re: Converting a column into an Array for nested do loops

Tom — Sun, 21 Jul 2019 16:59:09 GMT

It is trivial to take all of the observations for a single variable and rotate it up into many variables on one observation.

You could use proc transpose.

proc transpose data=have out=want;
  var amount ;
run;
data my_calculations;
  set want;
  array temp col: ;
  ...
run;

Or you could just load your array in your data step. Just make the array larger than you should ever need and use the actual number of values instead of the array dimension as the upper bound in your loops.

data my_calculations;
  array temp [10000] _temporary_;
  if _n_=1 then do i=1 to nobs;
    set have nobs=nobs;
    temp[i]=amount;
  end;
  ....
run;

Re: Converting a column into an Array for nested do loops

Reeza — Sun, 21 Jul 2019 20:23:00 GMT

You could also use data steps and the automatic loop to get things done. Given your current code, where you never use a, you can use this approach. I'm not sure what the value of having information in the log is via PUT statements versus having that stored in a data set that's much easier to query.


data have;
input temp;
cards;
200
203
205
206
208
250 
300
;
run;

data want;
set have end=eof;
retain cum_total;
if _n_ <= 5 then cum_total = sum(cum_total, temp);
if eof and cum_total > 300 then alert="Generated" ; 
else if eof then alert="Not Generated";
run;

@Kirotheninja wrote:

data alert;
ARRAY temp[7] (200 203 205 206 208 250 300);
put _all_;
/*Running nested do loops over the array*/
sum1=0;
do i=1 to dim(temp);
	c=0;sum2=0;
		do j=1 to dim(temp);
		put temp[i]=;
		put temp[j]=;
			a=abs((temp[i]-temp[j])/temp[i])*100;
			if a<5 then sum2=sum2+temp[j];
			put a=;
		c=sum2;
	if c>sum1 then sum1=c;
	put sum2=;
	put sum1=;
	end;
	end;
	drop i j  sum2 a c temp1-temp7;
/*Check with the threshold for AGP*/
threshold=300;
if sum1>threshold then alert="Generated" ; else alert="Not Generated";
run;
proc print;
run;

This is my intention. But here i'm having to put in the values into an array manually. is tthere any way i can reference these values automatically?

Re: Converting a column into an Array for nested do loops

Kirotheninja — Mon, 22 Jul 2019 06:47:31 GMT

Thanks a lot 🙂