BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

6 REPLIES 6
Reeza
Super User
Ksharp
Super User

Can you post a picture to show your output ?

AUTigers
Calcite | Level 5

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;

Cynthia_sas
SAS Super FREQ

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

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

  1. 0.096096096
  2. 5.903903904

0

0

4

4

  1. 0.089251777
  2. 3.910748223

0

0

1

1

  1. 0.032736044
  2. 0.967263956

0

0

0

0

0

0

0

0

11

11

  1. 0.218083917
  2. 10.78191608
  3. 0.222810378
  4. 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.

Cynthia_sas
SAS Super FREQ

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;


shoes_across_diff_proc.png

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 2354 views
  • 0 likes
  • 5 in conversation