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