Can anyone help with the following question?!
Print the top 2 sale for the last two years info
1. Assume the sale order table is in DB2 with the following colums
EXEC SQL DECLARE CUSTOMER_SALE TABLE
CUST_ID CHAR(6) NOT NULL,
SALE_DATE DATE NOT NULL,
SALE_AMOUNT DECIMAL(9, 2) NOT NULL
2. The top one sale get 10% bouns of his total sale of that year
The top second Sale get 5% bouns of his total sale of that year
3. Print the report below (Include 2015 and 2014)
Cust_Id Sale_Date Sale_Amount Bonus
----------- ------------- ------------------ --------
100001 2015-04-01 $12,000.00
2015-07-01 $18,000.00
The toal $30,000.00 $3000.00
xxxxxxx 2015-02-02 $18,000.00
2015-07-02 $10,000.00
The total $28,000.00 $1400.00
xxxxxxx 2014-04-01 $18,000.00
2014-07-01 $18,000.00
The toal $36,000.00 $3600.00
xxxxxxx 2014-02-02 $18,000.00
2014-07-02 $12,000.00
The total $30,000.00 $1500.00
Hi,
You have not provided any data, so am guessing a bit here. This code will show how to rank items. As for reporting this out, I generally just create a dataset exactly as I want the output to look, i.e. do all calculations in the data and then the report step is very simple.
data have;
infile datalines;
length sale_date $10;
input cust_id $ sale_date $ sale_amount;
year=substr(sale_date,1,4);
datalines;
10001 2015-03-01 12000
10001 2015-04-01 24000
10001 2015-05-01 9000
10001 2015-06-01 36000
10001 2014-08-01 12000
10001 2014-09-01 24000
10001 2014-10-01 9000
10001 2014-11-01 36000
10002 2015-01-01 8000
10002 2015-02-01 26000
10002 2015-03-01 17000
10002 2015-04-01 30000
;
run;
/* Collect maximum two values per id */
proc sort data=have;
by cust_id year descending sale_amount;
run;
data have (where=(rank in (1,2)));
set have;
by cust_id year;
retain rank;
if first.year then rank=1;
else rank=rank+1;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.