BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pankak
Calcite | Level 5

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*/

Please help ASAP

1 ACCEPTED SOLUTION

Accepted Solutions
vmurali
Calcite | Level 5

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;

View solution in original post

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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

pankak
Calcite | Level 5

I need only in one dataset in separate coloums

Reeza
Super User

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

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

vmurali
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1057 views
  • 3 likes
  • 4 in conversation