topic Re: Summarizing and grouping data in New SAS User

Summarizing and grouping data

dona17 — Sat, 01 Jul 2023 16:10:59 GMT

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;

Re: Summarizing and grouping data

Kurt_Bremser — Sat, 01 Jul 2023 16:21:48 GMT

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;

Re: Summarizing and grouping data

sbxkoenk — Sat, 01 Jul 2023 17:11:32 GMT

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

Re: Summarizing and grouping data

dona17 — Sat, 01 Jul 2023 17:14:51 GMT

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

Re: Summarizing and grouping data

dona17 — Sat, 01 Jul 2023 17:16:10 GMT

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;

Re: Summarizing and grouping data

dona17 — Sat, 01 Jul 2023 17:18:10 GMT

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

Re: Summarizing and grouping data

Kurt_Bremser — Sat, 01 Jul 2023 17:50:14 GMT

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.

Re: Summarizing and grouping data

sbxkoenk — Sat, 01 Jul 2023 19:01:52 GMT

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

Re: Summarizing and grouping data

Kurt_Bremser — Sat, 01 Jul 2023 20:27:44 GMT

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;

Re: Summarizing and grouping data

PaigeMiller — Sun, 02 Jul 2023 09:24:03 GMT

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

Re: Summarizing and grouping data

Kurt_Bremser — Sun, 02 Jul 2023 10:51:17 GMT

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.

Re: Summarizing and grouping data

archita — Sun, 02 Jul 2023 15:07:22 GMT

Hi,

Thank you for your valuable suggestions. Noted!

Re: Summarizing and grouping data

archita — Sun, 02 Jul 2023 15:08:54 GMT

Thanks,it is, working.

Re: Summarizing and grouping data

archita — Sun, 02 Jul 2023 15:11:20 GMT

Thank you, its working.

Re: Summarizing and grouping data

archita — Sun, 02 Jul 2023 15:12:21 GMT

thanks,it works.