topic Re: proc report in SAS Programming

proc report

SASPhile — Fri, 13 Nov 2009 18:30:38 GMT

In the code shown below:
if the flag is 'Y' in stead of highlighting the physician's name in yellow, how to delete, keeping the rest of the logic same.
proc report list NOWINDOWS data=crmrk_combine_bsm_final out=out_final1 HEADLINE MISSING;
COLUMN terrid bsm physician_name flag year,productgroup,(total_scripts reptot);
where terrid ne '';
define terrid/group ;
define bsm/group ;
DEFINE physician_name / group ;
DEFINE year / across '' ;
DEFINE productgroup / across;
DEFINE total_scripts / sum format=comma12. noprint ' ';
DEFINE flag/group noprint;
define reptot/computed format=comma12. ' ';
compute reptot;
if flag='Y' then do;
_C6_=.;
_C8_=.;
_C10_=.;
_C12_=.;
_C14_=.;
_C16_=.;
call define("physician_name",'style','style=[background=yellow]');
end;
else do;
_C6_=_C5_;
_C8_=_C7_;
_C10_=_C9_;
_c12_=_c11_;
_c14_=_c13_;
_c16_=_c15_;

end;
endcomp;
run

Re: proc report

abdullala — Fri, 13 Nov 2009 20:09:47 GMT

can you do data=crmrk_combine_bsm_final(where=(flag^='Y')) ?

Re: proc report

SASPhile — Fri, 13 Nov 2009 20:16:24 GMT

Nope.
The idea is:
if physicians A B C are in terrid 111 and A has flag Y and their respective totals are 500 600 500 each then we show the summary as:

terrid Physician total
111 B 600
111 C 500
-------------------------------------
111(total) 1600

we hide A from showing.

Re: proc report

Cynthia_sas — Fri, 13 Nov 2009 20:29:05 GMT

I'm curious how anybody would know about the "missing" 500. When I look at your mini-report and see 600 and 500, I really wonder how those 2 numbers added up to 1600.

But, you can't delete a report row from PROC REPORT -- once the procedure has started (which is what it seems you want to do). You'll have to pre-summarize/pre-process your data prior to PROC REPORT, get it all massaged and organized the way you want. I'd probably summarize and delete the rows all at the same time in a DATA step program.

cynthia

Re: proc report

SASPhile — Fri, 13 Nov 2009 20:48:05 GMT

well thats how the report is required.To hide phycisians whose flag is Y and yet add their scripts to the total.

Re: proc report

Cynthia_sas — Fri, 13 Nov 2009 21:10:23 GMT

Interesting. You'll just need to pre-process the data....if the obs with flag=Y are passed to PROC REPORT, then PROC REPORT will want to include them on the report.

cynthia

Re: proc report

SASPhile — Fri, 13 Nov 2009 22:10:24 GMT

is there a way to supress the record if physician eq ' '?

Re: proc report

Cynthia_sas — Fri, 13 Nov 2009 23:59:20 GMT

It depends on what you mean by "record" -- if by "record" you mean the observation in a dataset and deleting it with a DATA step program...then, yes... you could delete observations from a dataset where the value for the physician variable was missing or blank.

On the other hand, if by "record" you mean suppressing a report row that is being generated with PROC REPORT. You cannot suppress or delete a report row as PROC REPORT is generating the REPORT.

For example, if you have this data:
[pre]
Terrid Phys total flag
111 A 500 Y
111 B 600 N
111 C 500 N
112 D 100 N
112 E 100 Y
112 C 400 N
112 F 200 N
[/pre]

Then you have to decide whether you want a DETAIL report -- where you have one report row for every observation in the original data set or whether you want a SUMMARY report, where you have one report row which represents the summary for a "group" of observations (such as those observations grouped by TERRID).

If you want a DETAIL report -- then by default -- PROC REPORT would DISPLAY every observation -- unless you use a WHERE statement or pre-process the data (such as to get rid of the obs where FLAG = Y). Your choices for a DETAIL report are to DISPLAY every value for every obs -- where PROC REPORT respects the original ordering of the data. OR to use a variable, such as TERRID as an ORDER variable, in which case, the repetitious display of TERRID would be suppressed on some report rows.

It is easy with PROC REPORT, especially when you are generating a DETAIL report, to forget that PROC REPORT is building a report row for EVERY observation and then, adding summary lines, as instructed by your BREAK and RBREAK statements. If, on the other hand, you wanted a SUMMARY REPORT from the above data, then you would define TERRID as a GROUP item on the REPORT. Without the PHYS column on the report, the above data would collapse down to 2 report rows -- one row for TERRID 111 and a second row for TERRID 112. If you add a final summary line or overall report break to the report, then you'd have 3 report rows on your SUMMARY or GROUP report.

So the answer to your question really depends on whether you are trying to delete a REPORT row or whether you are trying to delete an observation -before- PROC REPORT handles the data.

cynthia

[pre]
data scrip;
length Phys $15;
infile datalines;
input terrid Phys $ total flag $;
return;
datalines;
111 A 500 Y
111 B 600 N
111 C 500 N
112 D 100 N
112 E 100 Y
112 C 400 N
112 F 200 N
;
run;

ods listing close;
options nodate nonumber center;
ods html file='terrid.html' style=sasweb;
proc report data=scrip nowd;
title 'TERRID and Phys as ORDER variables';
column terrid phys total;
define terrid / order;
define phys / order;
define total / sum;
break after terrid / summarize;
rbreak after/ summarize;
compute after terrid;
phys = 'Total';
line ' ';
endcomp;
compute after;
phys = 'Total';
endcomp;
run;

proc report data=scrip nowd;
title 'TERRID as group variable';
column terrid t_id n total;
define terrid / group noprint;
define t_id / computed 'Terrid';
define n / 'Phys Count';
define total / sum;
rbreak after / summarize;
compute t_id / character length=8;
t_id = put(terrid,3.0);
endcomp;
compute after;
t_id = 'Total';
endcomp;
run;
ods html close;
[/pre]

Re: proc report

Peter_C — Sat, 14 Nov 2009 09:16:15 GMT

SASPhile
when I have had to do something similar, rather than delete the whole line, I collapsed all lines to be excluded into a single entry labelled something like "others" on a pie chart.
Another comparable data model would be the phone bill items below the 10cents level. There is probably some latin phrase to describe it;-)
My preprocessing summarised the "to be hidden" items. with the same cross-classifications as the report then merged those "hidden items totals" back among the "to be shown items". After that the summary lines were treated like normal data lines in the report.
It might be possible to achieve this kind of data handling in proc report, but it is beyond my imagination,how!
I imagine most of us could come up with working code, if that would be a satisfactory solution design for you and your customer/boss.

hth
PeterC

Re: proc report

Peter_C — Sat, 14 Nov 2009 09:29:22 GMT

thank you Cynthia
You've clarified the solution for me!
Preprocess the data, setting physician details to 'hidden', say. Then make proc report perform a summary - including physician identity at the lowest level. Those flagged Y will be counted but not identified.
great.

PeterC

Re: proc report

SASPhile — Mon, 16 Nov 2009 14:47:28 GMT

Hi cunthia,
The output for the first proc report is this:
terrid Phys total
111 A 500
B 600
C 500
111 Total 1600

112 C 400
D 100
E 100
F 200
112 Total 800

Total 2400

I'm trying for this:(A & E are not shown).but total at terrid level includes the sum of all physicians,.
we may say not displaying A and E or hiding A & E.
terrid Phys total
111 B 600
C 500
111 Total 1600

112 C 400
D 100
F 200
112 Total 800

Total 2400

Re: proc report

Cynthia_sas — Mon, 16 Nov 2009 19:57:02 GMT

Hi:
As I said, you're not going to be able to hide those report rows using PROC REPORT techniques. You'll have to pre-process the data.

You could either use PROC MEANS to summarize the Y flag totals (or PROC SQL or PROC TABULATE) and then create a subset of just the N flag observations. You could then either MERGE or use PROC SQL to merge the Y totals back with the N-only observations -- you'd have to be careful to only grab the Y totals once. But then you could pass the "massaged" data to PROC REPORT.

Imagine that your final "massaged" data looked something like this:
[pre]
terrid Phys FLAG total add_y add_cnt
111 B N 600 500 1
111 C N 500 . .
112 D N 100 100 1
112 C N 400 . .
112 F N 200 . .
[/pre]

This data set has only the N flag observations. But the first TERRID observation also holds the total for ADD_Y (this would have come from PROC MEANS) and the ADD_CNT var holds the total count of Y flags for each TERRID (just in case you wanted to go back to the original data and double check).

With this kind of structure to the data, you can now have a compute block that takes the ADD_Y sum and adds it to the TOTAL sum at the summary break. Something like this COLUMN statement and COMPUTE block:
[pre]
column terrid Phys add_y total;
. . . more code . . .
define add_y/ sum noprint;
define total / sum;
break after terrid / summarize;
compute after terrid;
total.sum + add_y.sum;
line ' ' ;
endcomp;
rbreak after / summarize;
compute after;
total.sum + add_y.sum;
endcomp;
[/pre]

But the PROC REPORT is not hiding anything -- your pre-processing gets rid of the Y observations, but makes their totals available as a NOPRINT item -- once the "massaged" data has the suggested structure. And, even though the data row for physician B and the data row for physician D is "holding" the Y flag total, the physician total is unchanged and since the ADD_Y item is a NOPRINT item, it will only be seen or included in the summary line -- it has no impact on the individual physician report row.

cynthia