topic Re: Array/Do Loop with Unknown Number of Iterations...? in SAS Data Management

Array/Do Loop with Unknown Number of Iterations...?

Matthew — Mon, 14 Dec 2015 16:13:51 GMT

Hi all,

I have a dataset that looks like this:

I need to get the information for each BUID into a single row. I know how to do this with arrays and do loops, but only when there's a fixed number of iterations, and I know what that number is. In this case, some BUIDs have more purchases than others for a given time period, and so the number of observations (and times the loop has the iterate) is variable.

Currently my code looks like this:

data rfm162.fy13_p4_array2;
retain
booked_date1-booked_date100
dmd1-dmd100
mbtwn1-mbtwn100
;

rename count=order_count_fy13_p4 RNCY=rncy_today;
label RNCY='rncy_today';

set rfm162.fy13_p4_array;
by curr_wg_buid booked_date;

format booked_date1-booked_date100 datetime20.;

array bd(100) booked_date1-booked_date100;
array money(100) dmd1-dmd100;
array mbtwn(100) mbtwn1-mbtwn100;

do i=1 to 100;

if i>count then do;
bd(i)=.;
money(i)=.;
mbtwn(i)=.;
continue;
end;
if i=count then do;
bd(i)=booked_date;
money(i)=dmd;
end;
if i>=2 then do;
m=i-1;
mbtwn(i)=((bd(i)-bd(m))/2592000);
end;
end;

dmd_average_fy13_p4=mean(of dmd1-dmd100);
dmd_total_fy13_p4=sum(of dmd1-dmd100);

mbtwn_average_fy13_p4=mean(of mbtwn1-mbtwn100);

drop dmd1-dmd100 mbtwn1-mbtwn100 dmd booked_date booked_date1-booked_date100 i m;
if last.curr_wg_buid;

run;

This code works, but it's messy in the sense that I might be cutting off a couple of transactions for some customers who made more than 100 transactions. Additionally, when I'm looking at a shorter time period, I've got to either do freq to figure out what the new max number of observations is, or make a relatively arbitrary guess. How can I get the do loop to figure out how many iterations it should loop for me in order to match the maximum number of observations for each BUID? I've got a feeling it has something to do with the *, based on my research, but I haven't been able to get that to work. I get weird "variable outside of array" type messages.

Thanks,

Re: Array/Do Loop with Unknown Number of Iterations...?

Reeza — Mon, 14 Dec 2015 16:22:31 GMT

You'll have to scan your data to find the maximum since you do need to declare a length for the arrays. You can store it in a macro variable and use that to declare your array lengths.
I would say, that's a inefficient/bad way to store your data, especially if you have 100 transactions per customer.

array bd(&array_size) booked_date1-booked_date&array_size;
array money(&array_size) dmd1-dmd&array_size;
array mbtwn(&array_size) mbtwn1-mbtwn&array_size;

Re: Array/Do Loop with Unknown Number of Iterations...?

RW9 — Mon, 14 Dec 2015 16:22:59 GMT

Why not just use three transposes and merge the result back. This is probably the simplest method as you don't need to know number of elements. I posted about this in a very similar topic earlier:

https://communities.sas.com/t5/Base-SAS-Programming/sas-enquiry/m-p/239140#M43961

Otherwise, you can get the number of elements by:

proc sql noprint;
  select max(element_number) 
  into  :max_elements
  from have;
quit;

data want;
  array a{&max_elements.} 8.;
  do i=1 to &max_elements.;
....

Why the need to transpose? Is it just for for an output file, if not then remain with the normalised structure, its far easier to program with.

Re: Array/Do Loop with Unknown Number of Iterations...?

Reeza — Mon, 14 Dec 2015 17:01:19 GMT

One more option - a macro written by users here:

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/TASS/Tabachneck-FlipTranspose.pdf

Re: Array/Do Loop with Unknown Number of Iterations...?

Matthew — Mon, 14 Dec 2015 17:19:47 GMT

This looks great, exactly what I need. Thank you!