topic Re: group by display in SAS Programming

group by display

SASPhile — Mon, 20 Jul 2009 14:39:41 GMT

I have 5 fields terrid physician city state productgroup total.
Productgroup has two categories "competitor" and "mydrug".
I do a group by on all 5 fields in proc report.
The issue is, the report shows only the productgroup present for the combination as shown below:
for terrid 6789 mydrug is not displayed as it is not presnt for that combination of group by variables.

terrid physician city state productgroup total
1234 abc_name pqr mn competitor 250
1234 abc_name pqr mn mydrug 350
6789 def_name jkl zx competitor 150

How to show productgroup "mydrug" for the last record total as 0 even if its not present. as shown below:
terrid physician city state productgroup total
1234 abc_name pqr mn competitor 250
1234 abc_name pqr mn mydrug 350
6789 def_name jkl zx competitor 150
6789 def_name jkl zx mydug 0

And when we export it to excel using ods statement,how to have the product group wraped into single cell instead as two rows?
Thanks for your time.

Re: group by display

sbb — Mon, 20 Jul 2009 15:52:02 GMT

Appears you have a requirement to list all combinations of some key variable list - something that can be accomplished by creating a file of all combinations and then MERGE back in that file, setting the TOTAL variable to a zero value, unless the MERGE is contributed from the data file.

Have a look at the SAS documentation for the MERGE process and also you have a few options for generating your "all combinations" variable value list -- PROC SQL and PROC SORT NODUPKEY to start.

Then for the MERGE, you will want to have a BY statement. From the SAS support http://support.sas.com/ website, I used the SEARCH facility and found a few useful technical / conference papers - links are listed below.

Scott Barry
SBBWorks, Inc.

SAS website SAMPLES code - all combinations:
http://ftp.sas.com/techsup/download/sample/samp_lib/basesampEvery_Possible_Merge_Combination.html

MERGING vs. JOINING: Comparing the DATA Step with SQL
Malachy J. Foley
University of North Carolina at Chapel Hill, NC
http://www2.sas.com/proceedings/sugi30/249-30.pdf

Re: group by display

Cynthia_sas — Mon, 20 Jul 2009 17:18:42 GMT

Hi:
You will want to investigate the use of the COMPLETEROWS option with PROC REPORT. It can be used by itself, or in conjunction with the PRELOADFMT option and a user-defined format to allow you to control for these data situations:
1) you want a 0 row for some combination of group/order variables, especially when the variable appears in the data, but not in your particular combo of group/order variables. For example, you have 4 regions and 3 categories, but not all regions show all 3 categories, even though some regions -do- show all 3 (needs just COMPLETEROWS)
and/or
2) you want a 0 row to appear for some set of group variables, even if that combination does not appear in the data at all. (needs PRELOADFMT and COMPLETEROWS) (for example, you have 4 regions and 4 categories, but the 4th category is NOT in the data for ANY region and you want a 0 row for that 4th category on the report)

Alternately, you could use a data manipulation technique, such as Scott outlined.

As for the other request, I believe you may want the SPANROWS option -- which is a 9.2 option that allows a group or order variable to span multiple rows.

BTW, you only use ODS HTML or ODS CSV or ODS TAGSETS.EXCELXP to create a file that Excel can open. It is not technically an "export" to Excel -- only PROC EXPORT or the SAS Libname Engine for Excel create true binary Excel files. All the other ODS methods are creating ASCII text files that Excel can open.

cynthia