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;
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.