Proc report question

Reply
New Contributor
Posts: 2

Proc report question

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

Super User
Super User
Posts: 7,720

Re: Proc report question

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;

New Contributor
Posts: 2

Re: Proc report question

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...
Ask a Question
Discussion stats
  • 2 replies
  • 224 views
  • 0 likes
  • 2 in conversation