## DATA Step, Macro, Functions and more

Solved
Contributor
Posts: 22

I have a dataset like;

data test;

input cust_id trans_type \$15. gender \$1. trans_date ddmmyy10. tran_amt;

datalines;

11201 Entertainment f 25/08/2014 50.65

11205 Grocery       m 02/08/2014 90.00

11205 Travel        m 25/07/2014 23.00

11203 Travel        m 25/07/2014 28.00

11204 Grocery       m 25/07/2014 29.00

11205 Travel        m 25/07/2014 24.00

11205 Travel        m 25/07/2014 25.00

11206 Payment       m 21/08/2014 25.00

;

run;

I want following:

/*a. Total expense and maximum expense in each trans_type*/

/*b. In addition to this cust_id of the customers whose expense is maximum in each trans_type*/

Accepted Solutions
Solution
‎01-14-2015 05:05 PM
New Contributor
Posts: 2

proc sql;

create table tab1 as

select *, sum(tran_amt) as tot_expense, max(tran_amt) as max_expense from test

group by trans_type;

create table tab2 as

select cust_id  , trans_type  from tab1 where max_expense = tran_amt;

create table final_tab as

select a.*,b.cust_id as cust_with_max_exp from tab1 a, tab2 b

where a.trans_type = b.trans_type;

quit;

All Replies
SAS Super FREQ
Posts: 9,371

Hi:

What code have you tried? What is your final output or your desired result supposed to look like? Do you want a dataset or do you want a report (like RTF, PDF or HTML)? PROC TABULATE, PROC REPORT and PROC MEANS will all give you what you say you want for a and you say that you want to see the cust_id of the customers whose expense is the maximum for each trans_type - but how do you want to see this in a separate dataset or in a report? In a title? On each row of a table? PROC SQL will give you that and there are other ways to find out the cust_id of the customers with the max for each trans_type.

Showing the data is only part of the question. Showing the code you've tried to achieve the end result and then saying what's wrong with the code you've tried and how you want it to be different is really all part of the question. And, if you have need for immediate or urgent help, you are better off opening a track with Tech Support.

cynthia

Contributor
Posts: 22

I need only in one dataset in separate coloums

Super User
Posts: 23,771

a - proc means or proc summary or proc SQL - try proc summary

b - what do you want your output to look like.

Solution
‎01-14-2015 05:05 PM
New Contributor
Posts: 2

proc sql;

create table tab1 as

select *, sum(tran_amt) as tot_expense, max(tran_amt) as max_expense from test

group by trans_type;

create table tab2 as

select cust_id  , trans_type  from tab1 where max_expense = tran_amt;

create table final_tab as

select a.*,b.cust_id as cust_with_max_exp from tab1 a, tab2 b

where a.trans_type = b.trans_type;

quit;

🔒 This topic is solved and locked.