BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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
3 REPLIES 3
Peter_C
Rhodochrosite | Level 12
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
SASPhile
Quartz | Level 8
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
SASPhile
Quartz | Level 8
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!

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
  • 3 replies
  • 573 views
  • 0 likes
  • 2 in conversation