Original Data | ||||
LOB | MEMBER COUNT | PROGRAM NAME | PAID | |
MEDICAID | 150 | A | $300 | |
290 | B | $600 | ||
100 | C | $760 | ||
MEDICARE | 400 | A | $1,000 | |
350 | B | $400 | ||
180 | C | $350 |
Want output report like this:
REPORT FORMAT | ||||
SUMMARY BY LOB | LOB | MEMBER COUNT | PROGRAM NAME | PAID |
MEDICAID | 150 | A | $300 | |
290 | B | $600 | ||
100 | C | $760 | ||
TOTAL | 540 | |||
MEDICARE | 400 | A | $1,000 | |
350 | B | $400 | ||
180 | C | $350 | ||
TOTAL | 930 |
do not know how to
Please follow what @Reeza said. If you change paid to 'display' in my code, then it will give you what you wanted and yes, you should post code with the running man icon in your posts. It is rather hard to read the way you posted it.
PROC REPORT | DATA=DATA_SAMPLE | |||
COLUMNS | MEMBER COUNT | PROGRAM NAME | PAID ; | |
DEFINE | TOTAL/COMPUTED; | |||
DEFINE | LOB/ORDER; | |||
BREAK AFTER LOB/SUMMARIZED SUPPRESS; | ||||
COMPUTE BEFORE LOB; | ||||
TOB="TOTAL"; | ||||
TOTAL=SUM(MEMBER_COUNT); | ||||
ENDCOMP; | ||||
RUN; |
LOB="TOTAL"
data test;
input LOB $ count prog $ paid;
datalines;
MEDICAID 150 A 300
MEDICAID 290 B 600
MEDICAID 190 C 760
MEDICARE 400 A 1000
MEDICARE 350 B 400
MEDICARE 180 C 350
;
run;
proc report data=test split='*';
columns LOB count prog paid;
define LOB / ' *LOB' order order=internal;
define count / analysis 'Member*Count';
define paid / ' *Paid';
define prog / "Program*Name";
break after LOB / summarize;
compute after LOB;
LOB='Total';
endcomp;
run;
thank you, after run , the result.
LOB | MEMBERCOUNT | PROGRAMNAME | PAID |
MEDICAID | 150 | A | $300 |
290 | B | $600 | |
100 | C | $760 | |
TOTAL | 540 | $1,660 | |
MEDICARE | 400 | A | $1,000 |
350 | B | $400 | |
180 | C | $350 | |
TOTAL | 930 | $1,750 |
I do not want paid summary or computers, want remove $1,660, %1,750 and become blank , only memer_count need summarized and leave the rest of are blank
$1,660, $1,750 no needed, want to be blank, only Member_count needs summarized
Okay, maybe someone else can help you then. That is the way I know how to make total rows in proc report and I think it sums all the numeric variables so you might have to change paid column to char if it's not already.
ok, thank you !!
I might change paid to character to see if it can work...
Note that you used ANALYSIS after the total count column.
There are other designations for columns - DISPLAY and GROUP are the main other ones used. If you put a variable as DISPLAY it will not be summarized.
define paid / display ' *Paid';
@tarheel13 wrote:
Okay, maybe someone else can help you then. That is the way I know how to make total rows in proc report and I think it sums all the numeric variables so you might have to change paid column to char if it's not already.
total = sum(Count) by LOB, not need to sum the rest and showing on report , only member_count summary need to be report, leave the rest of them blank ... on the Total line ...
Please follow what @Reeza said. If you change paid to 'display' in my code, then it will give you what you wanted and yes, you should post code with the running man icon in your posts. It is rather hard to read the way you posted it.
OP why are you posting code and data as tables?
Please post code using the code blocks and data preferably as a data step but if you cannot figure that out, tables are fine but don't assume we will interpret them correctly as the format goes wacky many times.
@JHE wrote:
PROC REPORT DATA=DATA_SAMPLE COLUMNS MEMBER COUNT PROGRAM NAME PAID ; DEFINE TOTAL/COMPUTED; DEFINE LOB/ORDER; BREAK AFTER LOB/SUMMARIZED SUPPRESS; COMPUTE BEFORE LOB; TOB="TOTAL"; TOTAL=SUM(MEMBER_COUNT); ENDCOMP; RUN;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.