topic Re: Create a separate column to show sub-totals (by PRODUCT ID) in PROC REPORT in SAS Programming

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

cesarortiz — Tue, 03 Dec 2019 18:58:20 GMT

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;

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

NewSASPerson — Wed, 04 Dec 2019 14:05:55 GMT

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.

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

cesarortiz — Wed, 04 Dec 2019 14:23:55 GMT

Hi:

Will try that. Thanks!!!!