Looping with Arrays

[Scott86]

Hi everyone,

I am trying to create 4 columns, Qtr1-Qtr4, which sums up the total amount of orders per quarter for each customer by year. I am trying to do this with a looping array but I am having some difficulty. Please view my code below:

data quarters (drop = i Year order_year);
by customer_ID;
set tut.order_fact;
array Qtr_array {4} Qtr1-Qtr4;
Order_Year = intck('YEAR', Order_date, today(), 'C');
Year = min(order_year);
do i = 1 until (year => Order_year);
Qtr_array {i} = sum(total_retail_price);
Year = year + 1;
end;
run;

I've attached the dataset so you can view it. Please let me know if I need to clarify anything.

Thanks for any help.

Regards.

[novinosrin]

I can't open attachments, however a quick look at your  code tells me, your index variable i in do iterative loop will remain 1 for all iterations  until the  until expression is true as i doesn't seem to increment anywhere in the inside the loop

Also, your by statement should follow set statement or in other words set statement should precede by statement

[novinosrin]

You could try a simple approach:

proc sort data=order out=_order;
by customer_id order_date;
run;

data _order1;
set _order;
year=year(order_date);
qtr=qtr(order_date);
run;

proc sql;
create table want as
select customer_id, year, qtr, sum(total_retail_price) as amount
from _order1
group by customer_id, year, qtr
order by customer_id,year,qtr;
quit;

proc transpose data=want out=final_want;
by customer_id year;
var amount;
id qtr;
run;

[Scott86]

Thanks guys, I am trying to practice my array and looping programs because its a weak area of mine so would like to solve this using an array. 

[art297]

If you're trying to practice arrays, try to write a data step to accomplish the same thing that my suggested proc summary does, then write another data step that does what my suggested proc summary does. Hint: first sort the file by Order_Date.

Art, CEO, AnalystFinder.com

[art297]

I think that the following does what you want:

proc summary data=tut.order_fact nway;
  var Total_Retail_Price;
  class customer_ID Order_Date;
  format Order_date yyq6.;
  output out=need (drop=_:) sum=Sum_Retail_Price;
run;


proc transpose data=need out=want (drop=_:) prefix=Total_Price_;
  var Sum_Retail_Price;
  by customer_ID;
  id Order_date;
  format Order_date yyq6.;
run;

Art, CEO, AnalystFinder.com

[Reeza]

Thanks guys, I am trying to practice my array and looping programs because its a weak area of mine so would like to solve this using an array. 

Knowing when to use which approach to use when is something more important to learn.

Because your data is in a long form using PROC MEANS and TRANSPOSE is more efficient. If you use arrays things you need to account for:

1. Determining boundaries of each year/quarter

2. Summarizing records first

3. Retaining data across rows

4. Accounting for the last record, if it's not a full year

Arrays really don't work well for long data sets, since arrays in SAS are only shortcut references to variable names. In other languages they're very different. 

---

@Scott86 wrote:

Hi everyone,

I am trying to create 4 columns, Qtr1-Qtr4, which sums up the total amount of orders per quarter for each customer by year. I am trying to do this with a looping array but I am having some difficulty. Please view my code below:

data quarters (drop = i Year order_year);
by customer_ID;
set tut.order_fact;
array Qtr_array {4} Qtr1-Qtr4;
Order_Year = intck('YEAR', Order_date, today(), 'C');
Year = min(order_year);
do i = 1 until (year => Order_year);
Qtr_array {i} = sum(total_retail_price);
Year = year + 1;
end;
run;

I've attached the dataset so you can view it. Please let me know if I need to clarify anything.

Thanks for any help.

Regards.

---

[PGStats · Accepted solution]

Building on @novinosrin's suggestion,

proc sort data=order out=_order;
by customer_id order_date;
run;

data _order1 / view=_order1;
set _order;
year=year(order_date);
qtr=qtr(order_date);
run;

data quarters;
array q qtr1-qtr4;
do until (last.year);
    set _order1; by customer_id year;
    q{qtr} = sum(q{qtr}, total_retail_price);
    end;
keep customer_id year qtr1-qtr4;
run;

[Scott86]

Thanks guys,

I got kinda close but needed your loop array to complete it :(. 

My code was:

data Qtrs;
set order1;
by customer_ID year;
array qtr_array {4} Qtr1-Qtr4;
do year = 2007 to 2011;
qtr_array {4} = sum(total_sales);
end;
run;

How do I set it so if there are no sales in the Qtr is shows as 0? also can I format within the array so Qtr are in Dollar9.?

[PGStats]

Replace last step with:

data quarters;
array q qtr1-qtr4;
format qtr1-qtr4 dollar9.;

do i = 1 to 4; 
    q{i} = 0; 
    end;

do until (last.year);
    set _order1; by customer_id year;
    q{qtr} = q{qtr} + total_retail_price;
    end;

keep customer_id year qtr1-qtr4;
run;