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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.