1

I would like to add summary record after each group of records connected with specific shop. So, I have this: 

Shop_id Trans_id Count  
      1        1    10   
      1        2    23  
      1        3    12  
      2        1     8  
      2        2    15

And want to have this:

Shop_id Trans_id Count  
      1        1    10  
      1        2    23  
      1        3    12  
      .        .    45  
      2        1     8  
      2        2    15  
      .        .    23

I have done this using PROC SQL but I would like to do this using PROC REPORT as I have read that PROC REPORT should handle such cases.

max04
  • 5,315
  • 3
  • 13
  • 21
  • Are you looking to create an output data set with the summary record or just a printout? If you just need a printout use `PROC PRINT` with a `BY` statement for `Shop_id` and a `SUM` statement for `Count`. – J_Lard Jan 30 '18 at 13:59
  • Output data set with the summary record. – max04 Jan 30 '18 at 15:01
  • It's not a good idea to store totals in your main data set unless you're using it to report later on, and it's still generally easier to use PROC REPORT/PRINT. That being said, if you want the totals, PROC MEANS is the easiest method, use a CLASS statement and it will add in subtotals and totals automatically. – Reeza Jan 30 '18 at 15:50
  • I know but this is a requirement from a client :) A the end, I have to have a report with the main dataset including additional summary records. – max04 Jan 31 '18 at 10:22

1 Answers1

1

Try this:

data have;
input shop_id Trans_id Count;
cards; 
      1        1    10   
      1        2    23  
      1        3    12  
      2        1     8  
      2        2    15  
;
proc report data=have out=want(drop=_:);
define shop_id/group;
define trans_id/order;
define count/sum;
break after shop_id/summarize;
compute after shop_id;
  if _break_='shop_id' then shop_id='';
endcomp;
run;
Shenglin Chen
  • 4,504
  • 11
  • 11
  • Thank you Shenglin. We are almost there :) One thing, I want to have shop_id and trans_id in every row (above we get only distinct values and dots for the rest records). – max04 Jan 30 '18 at 21:46
  • I have tried this with some additional columns which I just want to show (no aggregations at all) and didn't work like here. Could you advise me how to just add for instance 2 additional columns with some data (for instance orderdate and person_id column)? – max04 Jan 31 '18 at 10:14