DATA Step, Macro, Functions and more

group by display

Super Contributor
Posts: 713

group by display

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.
Super Contributor
Super Contributor
Posts: 3,176

Re: group by display

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 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:

MERGING vs. JOINING: Comparing the DATA Step with SQL
Malachy J. Foley
University of North Carolina at Chapel Hill, NC
Posts: 9,371

Re: group by display

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)
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.

Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation