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!
I have merged all your posts that deal with the same issue into the original thread. Please keep one question in in one thread.
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;
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
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.