DATA Step, Macro, Functions and more

extra row

Reply
Super Contributor
Posts: 647

extra row

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
SAS Super FREQ
Posts: 8,743

Re: extra row

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
Super Contributor
Posts: 647

Re: extra row

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
SAS Super FREQ
Posts: 8,743

Re: extra row

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
Ask a Question
Discussion stats
  • 3 replies
  • 113 views
  • 0 likes
  • 2 in conversation