DATA Step, Macro, Functions and more

Looping with Arrays

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Looping with Arrays

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.


Accepted Solutions
Solution
‎01-31-2018 12:51 AM
Esteemed Advisor
Posts: 5,611

Re: Looping with Arrays

[ Edited ]

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;
PG

View solution in original post


All Replies
Super User
Posts: 2,037

Re: Looping with Arrays

[ Edited ]

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

Super User
Posts: 2,037

Re: Looping with Arrays

Posted in reply to 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;
Contributor
Posts: 29

Re: Looping with Arrays

Posted in reply to novinosrin

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. 

Super User
Posts: 8,213

Re: Looping with Arrays

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

 

Super User
Posts: 8,213

Re: Looping with Arrays

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

 

Super User
Posts: 23,928

Re: Looping with Arrays

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.


 

Solution
‎01-31-2018 12:51 AM
Esteemed Advisor
Posts: 5,611

Re: Looping with Arrays

[ Edited ]

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;
PG
Contributor
Posts: 29

Re: Looping with Arrays

Thanks guys,

 

I got kinda close but needed your loop array to complete it Smiley Sad

 

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

 

 

Esteemed Advisor
Posts: 5,611

Re: Looping with Arrays

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;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 236 views
  • 1 like
  • 5 in conversation