About Group_by aggregation functions

[PaigeMiller]

Since you have gender and age in the SELECT clause, you should not put those variables in the GROUP BY clause.

[Jianan_luna]

Thanks for your reply! I just change my coding like this:

data cus_tran;
	input  ID AGE $5. GENDER $ SUB_NUM TRANSACTION_DATE :yymmdd10. QUANTITY Profit;
	format TRANSACTION_DATE yymmdd10.;
	Datalines;
	1 10-19 F 0 2014-05-06 1 13
	1 10-19	F 1 2014-05-06 1 13
	1 10-19 F 1 2014-04-10 2 22
	2 10-19 M 1 2014-04-11 2 10
	3 30-40 F 1 2014-04-07 1 10
	4 10-19 F 0 2014-04-08 1 10
	3 30-40 F 0 2014-04-22 1 20
	5 20-30 M 1 2014-04-30 1 20
	2 10-19 M 0 2014-05-01 1 20
	3 30-40 F 0 2014-05-06 1 10
	4 10-19 F 1 2014-05-10 3 20
	4 10-19 F 0 2014-04-30 1 20
	5 20-30 M 1 2014-05-10 1 20
	2 10-19 M 0 2014-05-10 1 30
	3 30-40 F 0 2014-05-01 1 10
	2 10-19 M 1 2014-04-01 1 10
	3 30-40 F 1 2014-05-01 1 20
	;
RUN;

proc sql;
   create table want as
	SELECT  ID,put(TRANSACTION_DATE,monyy7.) as month_year, sum (Profit) as totalProfit, count (QUANTITY) as totalQuantity, 
			sum (SUB_NUM) as frequency, GENDER, AGE
	From cus_tran
	Group By ID, month_year;
quit;

However, the outcome is like the following. Now the data is not grouped by each specific ID. Can you please help me fix this? I just wonder can I use duplicate function? But that is for the duplicate row, not for specific duplicated cell. Can you help me? Thanks so much!

[Kurt_Bremser]

I have merged all your posts that deal with the same issue into the original thread. Please keep one question in in one thread.

[Jianan_luna]

Thanks!

[Jianan_luna]

Thanks so much

[Jianan_luna]

I just got a result table like this:

However, I want to edit my layout like this: for each specific customers, their corresponding age and gender, with total_profit/total_quantity, frequency on April, frequency on May, and with their gender and age. I am trying TRANSPOSE, however, I failed to transpose it. Could you please help me fix this? I put my coding following with this table as well. Thanks so much!

ID	total_profit only in April	total quantity (only on April)	frequency (only on April)	frequency (only on May)	gender	age
1
2

data cus_tran;
	input  ID AGE $5. GENDER $ SUB_NUM TRANSACTION_DATE :yymmdd10. QUANTITY Profit;
	format TRANSACTION_DATE yymmdd10.;
	Datalines;
	1 10-19 F 0 2014-05-06 1 13
	1 10-19	F 1 2014-05-06 1 13
	1 10-19 F 1 2014-04-10 2 22
	2 10-19 M 1 2014-04-11 2 10
	3 30-40 F 1 2014-04-07 1 10
	4 10-19 F 0 2014-04-08 1 10
	3 30-40 F 0 2014-04-22 1 20
	5 20-30 M 1 2014-04-30 1 20
	2 10-19 M 0 2014-05-01 1 20
	3 30-40 F 0 2014-05-06 1 10
	4 10-19 F 1 2014-05-10 3 20
	4 10-19 F 0 2014-04-30 1 20
	5 20-30 M 1 2014-05-10 1 20
	2 10-19 M 0 2014-05-10 1 30
	3 30-40 F 0 2014-05-01 1 10
	2 10-19 M 1 2014-04-01 1 10
	3 30-40 F 1 2014-05-01 1 20
	;
RUN;

proc sql;
   create table want as
	SELECT  ID,put(TRANSACTION_DATE,monyy7.) as month_year, sum (Profit) as totalProfit, count (QUANTITY) as totalQuantity, 
			sum (SUB_NUM) as frequency
	From cus_tran
	Group By ID,month_year;
quit;

[Tom · Accepted solution]

Let's translate the table headers into an SQL query.

select 
   ID
  ,sum(case when month(transaction_date)=4 then profit else . end)
     as total_profit_only_in_April
  ,sum(case when month(transaction_date)=4 then quantity else . end)
     as total_quantity_only_in_April
  ,count(case when month(transaction_date)=4 then quantity else . end)
     as frequency_only_in_April
  ,count(case when month(transaction_date)=5 then quantity else . end)
     as frequency_only_in_May
  ,max(gender) as gender
  ,max(age) as age
from cus_tran
group by id 
;

[Jianan_luna]

Thanks so much