topic Proc report question in SAS Programming

Proc report question

Stu_J — Thu, 17 Sep 2015 05:28:06 GMT

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

Re: Proc report question

RW9 — Thu, 17 Sep 2015 09:13:26 GMT

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;

Re: Proc report question

Stu_J — Thu, 17 Sep 2015 17:59:31 GMT

Hi Rw9, thanks for your reply. Yep, I didn't provide any data, so just make up some. The data is in DB2, I would like to use pass thought or libname to connect from there; for the report, I was trying to use proc report, but can't get the exact output as desired...