topic Re: tabulate in SAS Programming

tabulate

SASPhile — Wed, 05 Aug 2009 19:34:42 GMT

proc tabulate data=master_cvv1 format=5.;
class correct_name city state zip1 ghtm;
class ship_date / descending;
var compete nordi both;
table correct_name='Physician'*city='City'*state='State'*zip1='Zip'*Ghtm='GHTM',
ship_date=' '*(both='Total (mg)'*sum=' ' nordi='% Nordi'*pctsum=' '*F=7.1)
/ rts=33;
format ship_date yyq6.;
run;

For the above code I will have the columns as below:
2009Q2 2009Q1
Physician City State GHTM Total(mg) %Nordi Total(mg) %Nordi

How to get the a new column Total which is sum of total(mg) of 2009Q2 and 2009Q1 and display the report in the desceding order of the Total?
Thanks,
SASPhile

Re: tabulate

Peter_C — Wed, 05 Aug 2009 20:59:22 GMT

The ALL statistic can provide total over the SHIP_DATEs at
( ship_date=' '*(both='Total (mg)'*sum=' ' nordi='% Nordi'*pctsum=' '*F=7.1
ALL='2009H1'* both='Total (mg)'*sum=' ' )
however, order is more of a problem.
There is a proc statement option "order= data" that might help, but you would have to prepare the total of both quarters and apply to each, before sorting the data.
I'm not certain that that would work.
A first try: sort into order for totalling BOTH[pre] proc sort data= master_cvv1 out= prep1 ;
by correct_name city state zip1 Ghtm ;
run ;[/pre] * now a double pass, first to get the total across all ship_date (i.e. within GHTM ) ;
* second to output the data with the exta column=Tboth= total of "both" within GHTM;[pre]data prep2 ;
set prep1(in= first ) prep1( in= second ) ;
by correct_name city state zip1 Ghtm ;
if first.ghtm then tboth=0 ;
if first then tboth + both ;
if second then output ;
run ;[/pre]* finally sort into the output order and hope it "sticks" ;[pre]proc sort out= prep3 ;
by correct_name city state zip1 Ghtm
descending tboth ;
run ;[/pre]then run your tabulatelike[pre]proc tabulate data= prep3 format= 5. order=data ;[/pre]
and let us know how it goes

PeterC

Re: tabulate

SASPhile — Wed, 05 Aug 2009 21:13:12 GMT

Peter,
I tried the code but it wouldnt give the total of all the quaters.
2009Q2 2009Q1
Total (mg) % Nordi TOTAL Total (mg) % Nordi TOTAL

25056 9.4 25056 20798 12.6 20798

But the idea is to create this way:

total= 25056+20798

2009Q2 2009Q1 Total
Total (mg) % Nordi Total (mg) % Nordi
25056 9.4 20798 12.6 45854

Re: tabulate

SASPhile — Wed, 05 Aug 2009 21:28:14 GMT

Peter,
I tried this ad it worked:
table correct_name='Physician'*city='City'*state='State'*zip1='Zip'*Ghtm='GHTM',
ship_date=' '*(both='Total (mg)'*sum=' ' nordi='% Nordi'*pctsum=' '*F=7.1)
all=' '*(both='Annual Total (mg)'*sum=' '*F=7.)
/ rts=33;

Now I need to work on the dsceding order by the total!