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

[Matthew]

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,

[Reeza]

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;

[RW9]

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. 

[Reeza · Accepted solution]

One more option - a macro written by users here:

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

[Matthew]

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