BookmarkSubscribeRSS Feed
Stu_J
Calcite | Level 5

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Stu_J
Calcite | Level 5
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...

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 498 views
  • 0 likes
  • 2 in conversation