topic Re: Proc Report in SAS Programming

Proc Report

JHE — Fri, 25 Jun 2021 15:33:42 GMT

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

Re: Proc Report

Reeza — Fri, 25 Jun 2021 15:39:08 GMT

Please show your current code.

Re: Proc Report

JHE — Fri, 25 Jun 2021 15:47:50 GMT

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;

Re: Proc Report

JHE — Fri, 25 Jun 2021 16:04:09 GMT

LOB="TOTAL"

Re: Proc Report

tarheel13 — Fri, 25 Jun 2021 17:07:49 GMT

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;

Re: Proc Report

JHE — Fri, 25 Jun 2021 18:24:00 GMT

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

Re: Proc Report

JHE — Fri, 25 Jun 2021 18:25:50 GMT

$1,660, $1,750 no needed, want to be blank, only Member_count needs summarized

Re: Proc Report

tarheel13 — Fri, 25 Jun 2021 18:26:48 GMT

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.

Re: Proc Report

JHE — Fri, 25 Jun 2021 18:33:24 GMT

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 ...

Re: Proc Report

JHE — Fri, 25 Jun 2021 18:34:59 GMT

ok, thank you !!

I might change paid to character to see if it can work...

Re: Proc Report

Reeza — Fri, 25 Jun 2021 19:52:08 GMT

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.

Re: Proc Report

Reeza — Fri, 25 Jun 2021 19:54:10 GMT

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;

Re: Proc Report

tarheel13 — Fri, 25 Jun 2021 19:58:31 GMT

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.