topic Re: extra row in SAS Programming

extra row

SASPhile — Tue, 11 Aug 2009 20:07:22 GMT

region bsm lname fname city state zip prdgroup totaljan-june 2009-01..2009-06

for the above fileds:
prdgrp has two categories:
competitor
mydrug

how to create an extra row "total" under productgoup which is sum of competitor,mydrug.

region bsm lname fname city state zip prdgp total jan-june 2009-01...2009-06
competitor 2,000 50
mydrug 1,000 75
Total 3,000 125

Re: extra row

Cynthia_sas — Tue, 11 Aug 2009 23:31:48 GMT

Hi:
Do you want a final output dataset? Do you want a final REPORT -- using ODS and PROC REPORT or PROC TABULATE or PROC PRINT? What is the ultimate disposition of the information you want to convey?

You show some variable names, but no data. Is there one observation for each combo of region, bsm, lname, fname, city, state, zip and prdgroup? Are there multiple observations? Do you want to replicate the data in a dataset or table, but only add a summary observation? Do you want a report with a summary row?

What has your approach been so far? (PROC PRINT, PROC SQL, PROC REPORT, PROC TABULATE)?? Without some idea of what the data looks like and whether it needs to be summarized or is already summarized and some idea of whether you want a dataset or a report, I feel that there's not enough information to offer a suggestion.

cynthia

Re: extra row

SASPhile — Wed, 12 Aug 2009 00:35:55 GMT

Cynthia,
I have a dataset with these variables:
firstname,lastname address1 city state zip region ghtm productgroup year year_month mg_dispensed.

before going to the final step which being generating report(I thought of using proc report) I used proc summary with "var" mg_dispensed and rest all as class variables.

Productgroup has two values (competitor and my drug).year has only one value 2009.

The report will be displayed at year and year_month .In the productgroup, I should be able to display total as shown below.
(var1 to varn are firstname,lastname address1 city state zip region ghtm )to optimize space I use this representation.

total
var1--varn prdgrp jan2009-mar2009 2009-01 2009-02 2009-03
comp 120 20 40 60
mydrug 190 60 50 80
total 310 80 90 140

total jan2009-june2009 is sum of individual months for comp,mydrug.

The data is messy , there is a chance of having more than one observation for each combo of region, bsm, lname, fname, city, state, zip and prdgroup.

SASPhile

Re: extra row

Cynthia_sas — Wed, 12 Aug 2009 15:33:11 GMT

Hi:
This data seems familiar...with the detail information on address and bsm and drugs and physicians. I think the only thing that's missing from this list of data is terr_id. I'm still having a hard time figuring out whether you want a detail report or a summary report. Whether you want ACROSS variables in PROC REPORT or COLUMN variables in PROC TABULATE? Is the "JAN2009-MAR2009" column in the data or must it be summarized from the data?

Without seeing some of the actual input data (instead of just the column names), it's hard to understand how this data is different from any of this other data in these previous postings...or why the previous examples of PROC REPORT and PROC TABULATE don't work for this data. Everything in these previous postings about REPORT vs TABULATE still applies:

http://support.sas.com/forums/thread.jspa?messageID=20472俸
http://support.sas.com/forums/thread.jspa?messageID=20574偞
http://support.sas.com/forums/thread.jspa?messageID=23216媰
http://support.sas.com/forums/thread.jspa?messageID=20499倓
http://support.sas.com/forums/thread.jspa?messageID=20354侂

cynthia