topic Re: Looping with Arrays in SAS Programming

Looping with Arrays

Scott86 — Sun, 28 Jan 2018 22:48:41 GMT

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.

Re: Looping with Arrays

novinosrin — Sun, 28 Jan 2018 23:00:11 GMT

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

Re: Looping with Arrays

novinosrin — Sun, 28 Jan 2018 23:51:49 GMT

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;

Re: Looping with Arrays

Scott86 — Mon, 29 Jan 2018 00:35:36 GMT

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.

Re: Looping with Arrays

art297 — Mon, 29 Jan 2018 00:35:39 GMT

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

Re: Looping with Arrays

art297 — Mon, 29 Jan 2018 00:46:09 GMT

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

Re: Looping with Arrays

Reeza — Mon, 29 Jan 2018 01:13:00 GMT

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.

Re: Looping with Arrays

PGStats — Mon, 29 Jan 2018 05:09:15 GMT

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;

Re: Looping with Arrays

Scott86 — Wed, 31 Jan 2018 05:51:38 GMT

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

Re: Looping with Arrays

PGStats — Wed, 31 Jan 2018 06:00:40 GMT

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;