BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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.
2 REPLIES 2
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 658 views
  • 0 likes
  • 3 in conversation