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;
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;
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;
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;
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
Thanks,it is, working.
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;
Thank you, its working.
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.
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
Thank you , I need to do it via data step, but I will try as you suggested.
@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 coursebut 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).
Hi,
Thank you for your valuable suggestions. Noted!
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.
thanks,it works.
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.
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.