You may find these papers helpful
in understanding the data structure
you need to produce your output;
a macro to produce a frequency of response data set
from multi-response data
a macro to produce a data set with frequency of combinations of responses
from multi-response data
SAS community wiki page:
Ron Fehd the macro maven CDC Atlanta GA USA RJF2 at cdc dot gov
> -----Original Message-----
> From: email@example.com
> [mailto:firstname.lastname@example.org] On Behalf Of Paul Miller
> Sent: Friday, August 21, 2009 11:11 AM
> To: SAS(r) Discussion
> Subject: Getting SAS to Display Combinations of Values not in the Data
> Hello Everyone,
> I have a question about how to get SAS to display values not
> in the data when making frequency tables. I want to use Proc
> Report and the ODS msoffice2k destination to make Word tables
> that tabulate the relationship between race and whether or
> not a given adverse event (e.g. neutropenia) was drug
> related. Whether or not a given adverse event was drug
> related was assessed using the following categories:
> definitely, probably, possibly, unlikely, and unrelated.
> I need to make tables that display all levels of drug
> relationship. The tables will display zero values using an
> underscore. The idea is to show all combinations but still
> have something that is easily readable.
> The problem I'm having is that, for some adverse events, not
> all combinations of drug relationship by race are present in
> the data. The tables tabulate this relationship at various
> treatment cycles (Cycle 2, Cycle 4, Cycle 6, End of
> Treatment). For some adverse events, a given level of
> relationship is present at one cycle but not another. For
> example, there might be a "definite" relationship to study
> drug for at least one racial group at Cycle 2 but not at
> other treatment cycles. For other adverse events though,
> there might not be a "definite" relationship to study drug
> for any racial group at any treatment cycle.
> And yet I need these combinations to show in my table.
> So my question is how best to do that. So far, I've been
> preparing my data using Proc Freq and ODS output along with
> SQL/Data Steps. If I could get Proc Freq to output a dataset
> that includes all combination, then this might be the way to
> go. I've tried using the sparse option along with ODS
> output/out = to generate the data I need but without much
> success. So I'm starting to think this just won't work.
> The data that I'm submitting to Proc Report take on a fairly
> complex structure and it's occurred to me to generate them
> using a self-join in SQL instead of using Proc Freq etc. My
> sense though is that there's no way to get Proc SQL to
> include missing levels of grouping variables. Possibly I'm
> wrong about that though.
> Another possibility might be to get Proc Report to show the
> missing levels. I had the idea that it might be possible to
> use the missing option in Proc Report along with formats to
> get the missing combinations to display but so far that hasn't worked.
> Sorry for what has turned out to be a very long post.
> Hopefully the detail I've provided will help people to
> understand what I'm trying to do and to suggest a possible solution.
> Looking for the perfect gift? Give the gift of Flickr!