BookmarkSubscribeRSS Feed
BigD
Calcite | Level 5
Hi,


My code, listed below, works well, but I would like to take it to the next level.
Is there a way to delete a column, say _c5_ if the total is less than a certain value (say 5)? I want to do this because there are many hospitals (columns) with one or two cases making the report too wide. Ideally, I would like to delete these columns, or even better ,"roll" them up into a single column. I can do this by preprocessing the data and then using tabulate, but I would like to try it all in one procedure.


ods html;
proc report data=pc3 nowd
headline
headskip
split='*';
WHERE program='Surgery'
AND LOC IN ('P','S') and hospital not in (&HOSPITAL_GROUP);

column pcc_description cmg_description hospital, separations r_total;

define pcc_description / group 'Program Cluster' ;
define cmg_description / group 'Case Mix Grp' ;

define hospital / across format=$h_name. 'Hospital';

define separations / analysis sum format=comma9.0 ' ';


define r_total / computed format=comma9.0 'Row*Total';
break after pcc_description / summarize ol skip;
compute r_total;
r_total=sum(_c3_, _c4_,_c5_, _c6_, _c7_, _c8_, _c9_, _c10_, _c11_,
_c12_, _c13_);
endcomp;
rbreak after / dol summarize;
run;

ods html close;
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
Hi:
I'm not sure what you mean by "delete a column". But here's some background information that may answer your question.

Proc Report writes report rows, based on the data in your data set. So, if you had this COLUMN statement:
column name age height;

then Proc Report would create a column cell for each of those items on every row. If you have 19 observations, then you would have 19 report rows, with 3 columns per row. One thing you might be thinking of doing is, for example, if the student's age is 11, you want to "delete" the height column on that row and that row only. Well, Proc Report would not let you "delete" a column's cell; you -could- blank out the value for the height column, based on the student's age. But you could not have only 2 column cells on the row for the 11 yr old and have 3 column cells on the row for the 12 yr old on the next report row.

But in a COMPUTE block, you could do something like this:
[pre]
COMPUTE HEIGHT;
if age = 11 then height = .;
ENDCOMP;
[/pre]

This would be the correct syntax, if age and height were both DISPLAY items on the report. Normally, you'd see the dot for the missing value in the height column for the 11 yr old, but you can use the MISSING option:
options missing = ' ';
in order to have Proc Report display missing values with a space -- effectively blanking out the value in the column cell, but not deleting the column for a report row.

Or, you might mean that you want to not display an entire column -- you can do that with the NOPRINT option on the DEFINE statement.

In an ACROSS scenario, you could use a COMPUTE block, with absolute column references to blank out the value in a column, but you would have to test every absolute column or use a DO loop and an ARRAY to check all the ACROSS columns.

You can "roll" up or summarize ACROSS variable columns. Again, it's not entirely clear what you mean by this, but perhaps this example will help. The 2 methods for "roll up" shown here are making an alias for a report item and using it twice on the report -- once under the ACROSS variable and once NOT under an ACROSS variable OR using a COMPUTE block. Example #1 shows all the report columns, Example #2 shows NOPRINT being used on the ACROSS columns. You see the "roll up", but not the individual product type columns.

There are also STYLE= syntax methods you can use to make your output narrower horizontally if you are routing your output to destinations that support style. You can search the forum postings for "very wide" to find the code examples that describe these techniques.

cynthia
[pre]
ods listing;
proc report data=sashelp.prdsale nowd nocenter;
title '1) Show all Report Items';
column country prodtype,actual actual=acttot comptot;
define country / group;
define prodtype / across;
define actual / sum "Actual under PT";
define acttot / sum "Alias Method";
define comptot / computed f=dollar12.2 "Compute Method";
compute comptot;
comptot = sum(_c2_,_c3_);
endcomp;
run;

ods listing;
proc report data=sashelp.prdsale nowd nocenter;
column country prodtype,actual actual=acttot comptot;
title '2) Use NOPRINT';
define country / group;
define prodtype / across noprint;
define actual / sum "Actual under PT" noprint;
define acttot / sum "Alias Method";
define comptot / computed f=dollar12.2 "Compute Method";
compute comptot;
comptot = sum(_c2_,_c3_);
endcomp;
run;
[/pre]
JackHamilton
Lapis Lazuli | Level 10
I think what BigD wants is a way to *conditionally* suppress the display of an entire column, based on calculations done inside PROC REPORT.

I'd like to be able to conditionally suppress the display of an entire *row* in a report.

As far as I know, neither can be done within PROC REPORT - you have to preprocess the data.
Cynthia_sas
SAS Super FREQ
Hi, Jack:
Yes, I agree -- to conditionally remove a whole column (or a whole row), you'd have to pre-process the data. In some instances, you'd have to pre-summarize and then pre-process -- depending on whether the condition was based on summarized numbers for the condition or not.

I've discovered that with truly complex reports, you may sometimes need to rely on the power of the Data step + REPORT and/or Data step + SAS Macros + REPORT to get the kind of report you want.

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4937 views
  • 0 likes
  • 3 in conversation