Summarizing and grouping data

[dona17]

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;

[Kurt_Bremser · Accepted solution]

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]

Hi,
Thanks its is working . But I am still struggling with the totalsales variable

[dona17]

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]

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]

Thanks,it is, working.

[Kurt_Bremser]

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]

Thank you, its working.

[Kurt_Bremser]

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]

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]

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

[PaigeMiller]

---

@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).

[archita]

Hi,

Thank you for your valuable suggestions. Noted!

[Kurt_Bremser]

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]

thanks,it works.