Date:         Tue, 15 Feb 2000 19:55:13 -0800
Reply-To:     Lauren E Haworth <haworthl@MINDSPRING.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Lauren E Haworth <haworthl@MINDSPRING.COM>
Organization: MindSpring Enterprises
Subject:      Re: Proc Tabulate

[This followup was posted to comp.soft-sys.sas and a copy was sent to the
cited author.]

In article <4A256887.00044F86.00@gw01.workcover.vic.gov.au>,
Philip_Crane@WORKCOVER.VIC.GOV.AU says...
> I have some data about patients visits to a doctor that I want summarise in
> a table.  The data is
>      Visit_date  Patient_id  Visit_type
>      2/2/2000  1    PY100
>      2/2/2000  1    PY102
>      2/2/2000  2    PY100
>      2/2/2000  3    PY100   etc
>
> I the table this should be shown as
> Visit type
>      Visit_date  Patient_count      PY100    PY102
>      2/2/2000                3                       3               1
>
> I can do Visit_date by visit_type but I am puzzled on how to count the
> number of patients.  The count may be equal to the number of visit_types if
> each patient only has one visit each day however generally a patient will
> have multiple visit_types on a given day.
>
> Thanks for any guidance.
> Philip
>
Philip,

The best way to do this is to compute the correct patient count
separately, and then combine that information with the rest of your data
before running TABULATE.

The code below uses PROC SORT and PROC SUMMARY to compute the patient
count. That result (PATCOUNT) is then merged back into the mean dataset.

By taking the mean of PATCOUNT in the table, you can pull in your
computed result. Why a mean? Because the same value of PATCOUNT has been
merged with every record for each VISDATE. The mean allows you to pick
off this value. This is a great trick any time you need to add in a
number not easily calculated using TABULATE.

data visits;
  input visdate mmddyy9. patient vistype $;
  counter=1;
  format visdate mmddyy9.;
cards;
2/2/2000  1    PY100
2/2/2000  1    PY102
2/2/2000  2    PY100
2/2/2000  3    PY100
2/3/2000  4    PY100
2/3/2000  4    PY102
2/3/2000  4    PY101
2/3/2000  6    PY100
;
run;

proc sort data=visits out=pats nodupkey;
    by visdate patient;
run;
proc summary data=pats;
        class visdate;
    var counter;
    output out=sumpats sum=patcount;
run;

data visits2;
   merge visits sumpats (keep=visdate patcount);
   by visdate;
run;

proc tabulate data=visits2 f=10.;
        class visdate vistype;
        var patcount;
        table visdate,
           patcount="# patients"*mean=" "
           vistype=" "*n=" ";
run;


Here is the resulting table:

--------------------------------------------------------------------
|                      |# patients|  PY100   |  PY101   |  PY102   |
|----------------------+----------+----------+----------+----------|
|visdate               |          |          |          |          |
|----------------------|          |          |          |          |
|02/02/00              |         3|         3|         .|         1|
|----------------------+----------+----------+----------+----------|
|02/03/00              |         2|         2|         1|         1|
--------------------------------------------------------------------


Hope this helps,

Lauren Haworth
author, "PROC TABULATE by Example"