topic Re: Proc Report Question in SAS Procedures

Proc Report Question

tmm — Wed, 12 Sep 2012 20:55:50 GMT

I have a table that is formatted like this:

who svrty_lvl_cd rec norec ref noref _rror_ _mhor_ _cmhga_ p_cmhga

111 1 2 3 2.00 3.00 0.9 2.0 1.0 7.0

111 2 1 4 1.00 4.00 0.7 1.0 3.0 0.1

111 3 0 5 0.00 5.00 1.1 0.2 0.3 0.5

111 4 0 6 0.00 6.00 1.3 0.6 0.9 0.2

222 1 5 7 5.00 7.00 11.1 15.7 12.1 9.1

222 2 0 12 0.00 12.00 0.0 1.1 1.5 2.1

222 3 1 15 1.00 15.00 0.2 3.2 1.4 1.7

222 4 0 9 0.00 9,.00 0.3 4.2 2.4 5.8

There are about 1900 of the distinct who's but they all have svrty_lvl_cd 1 through 4 formatted in the table this way. I want to create an html proc out report and started my code like this:

PROC REPORT NOWD DATA=ReAdm.FinalCHI2 SPLIT='/';

COLUMNS

who PROV_MKT SVRTY_LVL_CD REC NOREC REF NOREF _MHOR_ _CMHGA_ P_CMHGA _RROR_;

DEFINE who/GROUP 'TIN' STYLE(HEADER)={BACKGROUND=GREY} MISSING;

DEFINE PROV_MKT/GROUP 'MINOR MARKET' STYLE(HEADER)={BACKGROUND=GREY} MISSING;

DEFINE REC/GROUP '#READMITS / SVRTYCD=1' STYLE(HEADER)={BACKGROUND=ORANGE} MISSING;

COMPUTE REC;

IF SVRTY_LVL_CD = 1 THEN DO;

CALL DEFINE('REC');

END;

ENDCOMP;

DEFINE NOREC/GROUP '#NON-READMITS / SVRTYCD=1' STYLE(HEADER)={BACKGROUND=ORANGE} MISSING;

DEFINE REF/GROUP '#READMITS / EXPECTED / SVRTYCD=1' STYLE(HEADER)={BACKGROUND=ORANGE} MISSING;

DEFINE NOREF/GROUP '#NON-READMITS / EXPECTED / SVRTYCD=1' STYLE(HEADER)={BACKGROUND=ORANGE} MISSING;

DEFINE _MHOR_/GROUP 'ODDS / RATIO / SVRTYCD=1' STYLE(HEADER)={BACKGROUND=ORANGE} MISSING;

DEFINE REC/GROUP '#READMITS / SVRTYCD=2' STYLE(HEADER)={BACKGROUND=GREEN} MISSING;

DEFINE NOREC/GROUP '#NON-READMITS / SVRTYCD=2' STYLE(HEADER)={BACKGROUND=GREEN} MISSING;

DEFINE REF/GROUP '#READMITS / EXPECTED / SVRTYCD=2' STYLE(HEADER)={BACKGROUND=GREEN} MISSING;

DEFINE NOREF/GROUP '#NON-READMITS / EXPECTED / SVRTYCD=2' STYLE(HEADER)={BACKGROUND=GREEN} MISSING;

DEFINE _MHOR_/GROUP 'ODDS / RATIO / SVRTYCD=2' STYLE(HEADER)={BACKGROUND=GREEN} MISSING;

DEFINE REC/GROUP '#READMITS / SVRTYCD=3' STYLE(HEADER)={BACKGROUND=BLUE} MISSING;

DEFINE NOREC/GROUP '#NON-READMITS / SVRTYCD=3' STYLE(HEADER)={BACKGROUND=BLUE} MISSING;

DEFINE REF/GROUP '#READMITS / EXPECTED / SVRTYCD=3' STYLE(HEADER)={BACKGROUND=BLUE} MISSING;

DEFINE NOREF/GROUP '#NON-READMITS / EXPECTED / SVRTYCD=3' STYLE(HEADER)={BACKGROUND=BLUE} MISSING;

DEFINE _MHOR_/GROUP 'ODDS / RATIO / SVRTYCD=3' STYLE(HEADER)={BACKGROUND=BLUE} MISSING;

DEFINE REC/GROUP '#READMITS / SVRTYCD=4' STYLE(HEADER)={BACKGROUND=MAGENTA} MISSING;

DEFINE NOREC/GROUP '#NON-READMITS / SVRTYCD=4' STYLE(HEADER)={BACKGROUND=MAGENTA} MISSING;

DEFINE REF/GROUP '#READMITS / EXPECTED / SVRTYCD=4' STYLE(HEADER)={BACKGROUND=MAGENTA} MISSING;

DEFINE NOREF/GROUP '#NON-READMITS / EXPECTED / SVRTYCD=4' STYLE(HEADER)={BACKGROUND=MAGENTA} MISSING;

DEFINE _MHOR_/GROUP 'ODDS / RATIO / SVRTYCD=4' STYLE(HEADER)={BACKGROUND=MAGENTA} MISSING;

DEFINE _CMHGA_/GROUP 'GENERAL / ASSOCIATION / CHISQ' STYLE(HEADER)={BACKGROUND=YELLOW} MISSING;

DEFINE P_CMHGA/GROUP 'GENERAL / ASSOCIATION / PVALUE CHISQ' STYLE(HEADER)={BACKGROUND=YELLOW} MISSING;

DEFINE _RROR_/GROUP 'OVERAL SEVERITY / ADJUSTED ODDS / RATIO(CMH)' STYLE(HEADER)={BACKGROUND=YELLOW} MISSING;

RUN;

I need to find a way to split the recs and all the other columns so they are done by the svrty_lvl_cd and for instance any rec norec ref noref and etc that are svrty_lvl_cd 1 the header is orange and 2 is green then 3 is blue and then 4 is magenta. If you need more information let me know. This data is all in a table and the example of table is above.

Re: Proc Report Question

Reeza — Thu, 13 Sep 2012 02:09:29 GMT

Have you checked out the following?

http://support.sas.com/resources/papers/stylesinprocs.pdf

Re: Proc Report Question

Ksharp — Thu, 13 Sep 2012 04:44:09 GMT

Can you post a picture to show your output ?

Re: Proc Report Question

AUTigers — Thu, 13 Sep 2012 06:00:20 GMT

first define svrty_lvl_cd as ACROSS item. then define a format based on the value of svrty_lvl_cd.

for each value of svrty_lvl_cd, you assign a desired background color. then later use the format in style definition. hope the sample code will help you a bit.

proc format;

value $hfmt 'F'='orange'

'M'='blue';

run;

ods html;

proc report data=sashelp.class out=out nowd;

column name (sex,(height weight));

define name /order;

define sex/across order=internal style(header)={background=$hfmt.};

define height/display;

define weight/display;

run;

ods html close;

Re: Proc Report Question

Cynthia_sas — Thu, 13 Sep 2012 06:30:32 GMT

Hi:

I'm not sure I understand what you want to do, but it almost sounds to me like you want change the PROC REPORT headers dynamically based on the value of a variable?? Or something like that.

But you have another problem, Your CALL DEFINE statement inside the COMPUTE block is incorrect, and I would guess that you are getting an error message like this:

ERROR 252-185: The DEFINE subroutine call does not have enough arguments.

The other issue is that you cannot dynamically change the column headers in PROC REPORT using the CALL DEFINE statement. CALL DEFINE will only impact the "data" cells for each of the report items. The headers are either changed with a STYLE(HEADER) override (which would impact all the headers without regard to the value of some other variable) or you would possibly use a macro program to create a separate table for each group of observations and then change the header accordingly based on something like how you invoke the macro program.

Then, I notice that in your REPORT code, you have DEFINE statements that you repeat, over and over (the variables are NOREC REF NOREF _MHOR_) ... that's something else I don't understand. It almost seems like you want the value of your SVRTY_LVL_CD to be used as an ACROSS item, instead of a GROUP item. There are a lot of Forum examples of using ACROSS items with PROC REPORT. I think that your basic approach, as coded, needs to be redesigned differently. For one thing, with all your variables defined as GROUP usage, what do you expect or want to get in the data cells? A count? A percent? What "numbers" are going to appear in the data cells?

You might want to consider reading a bit more about PROC REPORT and figure out how PROC REPORT works, especially with ACROSS items. If all you want are COUNTS and PERCENTS in the cells, then you might want to consider using PROC TABULATE, which would give you the count (the N) automatically and you can ask for PCTN as a statistic in the report.

cynthia

Re: Proc Report Question

tmm — Thu, 13 Sep 2012 12:10:40 GMT

The table is set up like the example I show but I don't want svrty_lvl_cd to appear in the proc report. Right now I take the SAS table and export to Access and manipulate there and then output to Excel. The Excel looks like this:

TIN	MINOR MARKET	#READMITS SVRTYCD=1	#NON-READMITS SVRTYCD=1	#ADMISSIONS SVRTYCD=1	#READMITS EXPECTED SVRTYCD=1	#NON-READMITS EXPECTED SVRTYCD=1	ODDS RATIO SVRTYCD=1	#READMITS SVRTYCD=2	#NON-READMITS SVRTYCD=2	#ADMISSIONS SVRTYCD=2	#READMITS EXPECTED SVRTYCD=2	#NON-READMITS EXPECTED SVRTYCD=2	ODDS RATIO SVRTYCD=2	#READMITS SVRTYCD=3	#NON-READMITS SVRTYCD=3	#ADMISSIONS SVRTYCD=3	#READMITS EXPECTED SVRTYCD=3	#NON-READMITS EXPECTED SVRTYCD=3	ODDS RATIO SVRTYCD=3	#READMITS SVRTYCD=4	#NON-READMITS SVRTYCD=4	#ADMISSIONS SVRTYCD=4	#READMITS EXPECTED SVRTYCD=4	#NON-READMITS EXPECTED SVRTYCD=4	ODDS RATIO SVRTYCD=4	TOTAL #READMITS	TOTAL #NON-READMITS	OVERALL TOTALADMITS	#READMITS OVERALL EXPECTED	#NON-READMITS OVERALL EXPECTED	GENERAL ASSOCIATION CHISQ	GENERAL ASSOCIATION PVALUE CHISQ	OVERALL SEVERITY ADJUSTED ODDS RATIO (CMH)
010356992	MAINE	0	6	6	0.096096096 5.903903904	0	0	4	4	0.089251777 3.910748223	0	0	1	1	0.032736044 0.967263956	0	0	0	0	0	0	0	0	11	11	0.218083917 10.78191608 0.222810378 0.636906844	0

I think to get the proc report in a way that I can do it because I am so new to SAS I am going to just have to do another proc format command to get each svrty_lvl_cd and their data on 1 line for the TIN. The ways in which some have stated in here only confuse me more and the documentation I read confuses me as well. I was hoping since you can do an if statement in a compute to just put it there but I guess not.

Re: Proc Report Question

Cynthia_sas — Thu, 13 Sep 2012 17:22:15 GMT

Hi:

In fact, this kind of "banding" on the headers is probably more easier to achieve with PROC TABULATE (using style=<parent>) than with PROC REPORT (which does not have style=<parent> ability). For example, see these Tech Support notes:

http://support.sas.com/kb/25/401.html (horizontal banding)

http://www2.sas.com/proceedings/sugi29/085-29.pdf (horizontal and vertical banding)

Note that the banding shown in the above links could just be applied to the headers, and not to the data cells. You still did not say where the "numbers" were coming from -- have you precalculated the count? What procedure did you use? I am not sure about the 1. and 2. in the cell for #READMITS EXPECTED, however, generally, both TABULATE and REPORT will only put 1 number in 1 cell (you show 2 "lines" in one cell). Although PROC REPORT could do something like what you show, if you built a single variable from both values and inserted a "line feed" or "carriage return" between the two values.

Without an idea of what your data looks like, it's hard to provide better advice. Instead, I am attaching a simple example with PROC REPORT (#1a and #1b) PROC TABULATE (#2) getting the counts for sales and inventory for 3 regions in SASHELP.SHOES. Not a very exciting report, number-wise, but it shows the header behavior differences (see screenshot)

cynthia

proc format;
value $regcolr 'Asia'='pink'
                 'Canada'='yellow'
                 'Pacific'='lightgreen';
run;

ods listing close;
ods html file='c:\temp\shoes_across.html' style=sasweb;

proc report data=sashelp.shoes nowd;
   title '1a) Using REPORT and ACROSS';
     where region in ('Asia', 'Pacific', 'Canada')
           and product in ('Boot', 'Slipper');
     column product region,(sales inventory);
     define product / group style(column)=Header;
     define region / across ' '
            style(header)={background=$regcolr. color=black};
     define sales / n f=comma10. ;
     define inventory / n f=comma10. ;
run;

proc report data=sashelp.shoes nowd;
   title '1b) Using REPORT and ACROSS and CALL DEFINE';
   title2 'CALL DEFINE only changes "data cells" not header cells';
     where region in ('Asia', 'Pacific', 'Canada')
           and product in ('Boot', 'Slipper');
     column product region,(sales inventory);
     define product / group style(column)=Header;
     define region / across ' '
            style(header)={background=$regcolr. color=black};
     define sales / n f=comma10.;
     define inventory / n f=comma10. ;
     compute sales;
       call define('_c2_', 'style', 'style={background=pink}');
       call define('_c4_', 'style', 'style={background=yellow}');
       call define('_c6_', 'style', 'style={background=lightgreen}');
     endcomp;
     compute inventory;
       call define('_c3_', 'style', 'style={background=pink}');
       call define('_c5_', 'style', 'style={background=yellow}');
       call define('_c7_', 'style', 'style={background=lightgreen}');
     endcomp;
run;

proc tabulate data=sashelp.shoes f=comma10.;
title '2) Using TABULATE';
     where region in ('Asia', 'Pacific', 'Canada')
           and product in ('Boot', 'Slipper');
class product region;
classlev region /style={background=$regcolr. color=black};
var sales inventory / style=<parent>;
table product,
        region=' '*(sales inventory)*n=' ';
run;

ods html close;