proc report/tabulate with missing or undefined values

[Peter_Y]

Hello all:

I wonder how to properly display tables that include columns with and without missing values. For example, suppose a portion of patients was selected for a particular blood test, which returns +/- as results.In the dataset, we then have a variable test=0/1 for every patient. For each patient with test=1, result=0/1 is defined as blood test results. In the report, I want to display sth. like :

Blood Test		Test Results
Done	Not Done	Negative	Positive

Since result is not defined when test=0. I cannot find a good way to display the two columns in the same table. I can use missing option but the challenge is that I don't want to display a third (missing/not done) column under Test Results, as it is redundant.

Any suggestions?

Thanks,

Peter

[ballardw]

If you are doing this for Each Patient you maybe should look into Proc Report.

The test results can be done with two result variables if you want to where NegResult and PosResult are set to 1 and summarized as numeric using SUM . (Set to 0 when tested but not that result and N can give total tests and Mean yields % negative or positive in aggregates)

Stub of code for Tabulate

var NegResult PosResult;

table NegResult=''*sum='Negative Result'

        PosResult=''*sum='Positive Result'

;

[RW9]

Hi,

Get a list of patients first, then left join the results back or you could do it with a set of exists:

proc sql;

     create table WANT as

     select      A.USUBJID,

                    case when exists(select USUBJID from HAVE where USUBJID=A.USUBJID and TEST=1) then "Done" else "" end as COL1,

                    case when exists(select USUBJID from HAVE where USUBJID=A.USUBJID and TEST ne 1) then "Not Done" else "" end as COL2,

                    case when exists(select USUBJID from HAVE where USUBJID=A.USUBJID and RESULT ne 1) then "Negative" else "" end as COL3,

                    case when exists(select USUBJID from HAVE where USUBJID=A.USUBJID and RESULT=1) then "Negative" else "" end as COL3

     from        (select distinct USUBJID from DEMOG) A;

quit;

[Cynthia_sas]

Hi:

  This is nearly impossible to answer without a sample of your data and an example of the code you've tried. With both TABULATE and REPORT, the structure of your data set is critical to how you code each procedure. Based on your description, I envision data like this:

ID   Test   Result

1111    1      0

2222    0      0

3333    1      1

4444    1      1

5555    1      0
     

  Which is just a guess on my part, because you also said that the blood test returns +/-, so your data could look like:

ID   Test Result

1111    1      -

2222    0      -

3333    1      +

4444    1      +

5555    1      -
   

  But in either case, you then say that "I cannot find a good way to display the two columns in the same table", but your table doesn't show 2 columns, your sample table shows 4 columns. What you want to do is fairly easy with PROC TABULATE, if you define both TEST and RESULT as CLASS variables.
     

  Why do you show multiple lines underneath your column headers? Were you meaning to show names/IDs or some other grouping variable on every row? If so, I would expect to see some column to the left of Blood Test, which you don't show. So overall, I don't really understand what you want to show on the report. Can you post a sample of your data and the code you've tried (all the code, including ODS statements). Otherwise, can you explain, using the sample data and code below what you want that cannot be produced with REPORT and/or TABULATE???
      

Cynthia

data tests;
  infile datalines;
  input ID Test Result;
return;
datalines;
1111    1      0
2222    0      0
3333    1      1
4444    1      1
5555    1      0
;
run;
  
proc format;
   value tstfmt 0='Not Done'
                1='Done';
   value resfmt 0='Negative'
                1='Positive';
run;

options missing=' ';
 
ods html file='c:\temp\testresults.html';
proc tabulate data=tests;
title 'Tabulate Show Every ID' ;
  class id test result;
  table id,
        test='Blood Test' result='Test Results' ;
  keylabel n = ' ';
  format test tstfmt. result resfmt.;
run;
   
proc tabulate data=tests;
title 'Tabulate  Show Summary Info';
  class id test result;
  table test='Blood Test' result='Test Results' ;
  keylabel n = ' ';
  format test tstfmt. result resfmt.;
run;
  
proc report data=tests nowd;
  title 'Report';
  column id test result;
  define id / order;
  define test / across f=tstfmt.;
  define result / across f=resfmt.;
run;
  
proc report data=tests nowd;
  title 'Summary Report';
  column test result;
  define test /across f=tstfmt.;
  define result / across f=resfmt.;
run;
ods html close;