BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
dona17
Calcite | Level 5

Hi,

Can anyone please help me with the code.I have also attached the dataset.I need to create variable num_months that counts total months within each quarter that customer had an order.I also need to create variable total_sales  that should contain totalsales for each quarter within each customer_id .

proc sort data=blib.order_qtrsum out=s;
by customer_id order_qtr;
run;
data qtrcustomers;
set s;
retain num_months;
if first.order_month then num_months=1;
else num_months=num_months+1;
if last.order_month then output ;
run;
proc print;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

You need a BY statement to create the FIRST. and LAST. variables. And you need to use the correct variable for the determination of the quarter:

data qtrcustomers;
set s;
by customer_id order_qtr;
retain num_months;
if first.order_qtr
then num_months = 1;
else num_months + 1;
if last.order_qtr;
run;

View solution in original post

14 REPLIES 14
Kurt_Bremser
Super User

You need a BY statement to create the FIRST. and LAST. variables. And you need to use the correct variable for the determination of the quarter:

data qtrcustomers;
set s;
by customer_id order_qtr;
retain num_months;
if first.order_qtr
then num_months = 1;
else num_months + 1;
if last.order_qtr;
run;
dona17
Calcite | Level 5
Hi,
Thanks its is working . But I am still struggling with the totalsales variable
dona17
Calcite | Level 5
proc sort data=blib.order_qtrsum out=s;
by customer_id order_qtr;
run;
data qtrcustomers;
set s;
by customer_id order_qtr;
retain num_months;
if first.order_qtr then num_months=1;
else num_months+1;
if last.order_qtr  ;
retain total_sales;
if first.customer_id then total_sales=sale_amt;
else total_sales+sale_amt;
if last.customer_id;
run;
proc print;
run;
sbxkoenk
SAS Super FREQ

There is no need to use the RETAIN statement for total_sales if you are using a SUM statement. With a SUM statement (total_sales + sale_amt), there's an implicit RETAIN.

/* with retain */
retain total_sales;
if first.customer_id then total_sales=0;
total_sales = (total_sales + sale_amt);
if last.customer_id then output;

/* without retain stmt (but with implicit retain) */
if first.customer_id then total_sales=0;
total_sales + sale_amt;
if last.customer_id then output;

/* output should be the same twice (not tested) */

Koen

 
archita
Fluorite | Level 6

Thanks,it is, working.

Kurt_Bremser
Super User
data qtrcustomers;
set s;
by customer_id order_qtr;
retain num_months;
if first.order_qtr then num_months=1;
else num_months+1;
if last.order_qtr  ; /* this subsetting IF stops the data step iteration when the condition is false */
retain total_sales;
if first.customer_id then total_sales=sale_amt;
else total_sales+sale_amt;
if last.customer_id;
run;

With two different scopes, you should create two datasets, one for customers, the other for customers and quarters:

data
  qtrcustomers (keep=customer_id order_qtr num_months)
  customers (keep=customer_id total_sales)
;
set s;
by customer_id order_qtr;
if first.order_qtr
then num_months = 1;
else num_months + 1;
if last.order_qtr  then output qtrcustomers;
if first.customer_id
then total_sales = sale_amt;
else total_sales + sale_amt;
if last.customer_id then output customers;
run;
archita
Fluorite | Level 6

Thank you, its working.

Kurt_Bremser
Super User

Keep in mind that I cannot see your dataset. I am posting this from my tablet, where SAS is not available.

By posting data in code (DATA step with DATALINES), you can avoid this.

sbxkoenk
SAS Super FREQ

Hello,

 

Why are you doing this with a data step?

 

I would use PROC MEANS or PROC SUMMARY or PROC TABULATE or PROC REPORT 
or the SIMPLE.SUMMARY action in CASL (CAS Language SAS VIYA).
Two other actions that you can use: AGGREGATION.AGGREGATE and DATAPREPROCESS.RUSTATS.

Perfectly fine to do it with a data step of course

but procedures are often easier to use than a data step (data step requires some programming knowledge).

 

BR,

Koen

dona17
Calcite | Level 5

Thank you , I need to do it via data step, but I will try as you suggested.

PaigeMiller
Diamond | Level 26

@sbxkoenk wrote:

Hello,

 

Why are you doing this with a data step?

 

I would use PROC MEANS or PROC SUMMARY or PROC TABULATE or PROC REPORT 
or the SIMPLE.SUMMARY action in CASL (CAS Language SAS VIYA).
Two other actions that you can use: AGGREGATION.AGGREGATE and DATAPREPROCESS.RUSTATS.

Perfectly fine to do it with a data step of course

but procedures are often easier to use than a data step (data step requires some programming knowledge).

 

BR,

Koen


Lots of wisdom there from Koen, which I think @dona17 should pay attention to. In particular, Koen's recommendation to use one of many SAS PROCs is the best approach forward. We have seen time after time in this forum people (usually beginners or near-beginners) trying to write their own data step code to perform "summarizing and grouping", and struggle mightily to do this and sometimes even get the results wrong (and not even realize it). Data steps are not a good tool to perform "summarizing and grouping"! (although it is certainly possible to use data steps to perform "summarizing and grouping") SAS has already done the hard work to write this code to do "summarizing and grouping" for us, then SAS has carefully debugged it and this code has been proven in approximately 12 bazillion real world applications. In @dona17 's other thread, I show how this is done in PROC SUMMARY, it's very simple, and everyone can and will have extremely high confidence that the calculations are done properly. @dona17: please take the wisdom offered here and use PROC SUMMARY (or other SAS PROCs which do similar things).

--
Paige Miller
archita
Fluorite | Level 6

Hi,

Thank you for your valuable suggestions. Noted!

Kurt_Bremser
Super User

But if you want to calculate total_sales for each quarter (like num_months), then PROC MEANS does it in one step:

proc means data=blib.order_qtrsum nway;
class customer_id order_qtr;
var sales;
output out=qtrcustomers (drop=_TYPE_ rename=(_FREQ_=num_months)) sum()=total_sales;
run;

If you run out of memory (unlikely), sort first and use BY instead of CLASS.

archita
Fluorite | Level 6

thanks,it works.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 14 replies
  • 693 views
  • 0 likes
  • 5 in conversation