BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Scott86
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

9 REPLIES 9
novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Obsidian | Level 7

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
Opal | Level 21

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
Opal | Level 21

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
Super User

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
Opal | Level 21

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
Scott86
Obsidian | Level 7

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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