Create a separate column to show sub-totals (by PRODUCT ID) in PROC REPORT

[cesarortiz]

Hi there:

Have the following situation where I am using proc report. I have used the basic report and runs good. Now I have been asked to create an additional column as part of the COLUMN (NOT a ROW) statement where I need to include Sub-totals by ID#. The ID# is a unique variable which I defined as group. The same ID can be found multiple times and the Approval number reflects its frequency.

Any help would be highly appreciated

This my example basic code included below:

ID#                       INDUSTRY                      Approvals        Sub-Totals by ID (Needed variable)

985623    Computer & Programming 1            111

                                                                         123

                                                                         456                            

                Computer & Programming 2            123

                                                                           23                836 (Needed - sum of all approvals under ID  985623)                  

526314    Computer & Programming 3             896

                                                                          563

                                                                          896

                Computer & Programming 4             456

                                                                          563

                                                                            41             3,415 (Needed- sum of all approvals under ID  526314)      

Grand Total                                                    4,251             4,215

PROC REPORT DATA=FINAL NOWD MISSING;
COLUMN ID INDUSTRY  APPROVALS;
DEFINE ID /GROUP DESCENDING ORDER=FREQ 'ID';
DEFINE INDUSTRY/GROUP 'INDUSTRY';
DEFINE APPROVALS/ SUM 'APPROVALS';
BREAK AFTER/SUMMARIZE;

RUN;

[NewSASPerson]

data have;
length industry $25;
infile datalines dsd truncover dlm=",";
input ID   industry $ Approvals;
	datalines;
985623, Computer & Programming 1,111
985623, Computer & Programming 1,123
985623, Computer & Programming 1,456
985623, Computer & Programming 2,123
985623, Computer & Programming 2,23  
526314, Computer & Programming 3,896
526314, Computer & Programming 3,563
526314, Computer & Programming 3,896
526314, Computer & Programming 4,456
526314, Computer & Programming 4,563
526314, Computer & Programming 4,41
 ;
 run;
data want;
set have;
by descending id;
firstid = first.id;
lastid = last.id;
run; 



PROC REPORT DATA=want NOWD MISSING;
COLUMN ID INDUSTRY  APPROVALS ID_Total firstid lastid dummy;
DEFINE ID /GROUP order = data;
DEFINE INDUSTRY/ group 'INDUSTRY';
DEFINE APPROVALS/analysis 'APPROVALS';
define id_total/"ID TOTAL" computed;
define dummy/computed noprint;
define firstid/display noprint;
define lastid/display noprint;
rbreak after/summarize;
*compute id_total;

compute dummy;
dummy = _break_;

if firstid = 1 then do;
h_id = 0;
end;

if missing(_break_) = 1 then do;
h_id  + approvals.sum;
end;

if lastid = 1 then do;
id_total = h_id;
end;
endcomp;

compute after;
id_total = approvals.sum;
endcomp;
run;

I got help here on something similar in the past. I hope this helps.

[cesarortiz]

Hi:

Will try that. Thanks!!!!