Date:         Tue, 17 Oct 2006 11:29:37 -0500
Reply-To:     SAS_learner <proccontents@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         SAS_learner <proccontents@GMAIL.COM>
Subject:      Re: How to do this in SQL ??
Comments: To: Jack Clark <JClark@chpdm.umbc.edu>,
          Wensui Liu <liuwensui@gmail.com>
In-Reply-To:  <8B5E9B95CC245C46A68BF4495A29A7EEB3C3B6@chpdm-mail.chpdm.umbc.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

hello Jack and Liu, I am doing something you guys asked Look at Log Inseted
of getting one Row extra which is Total at the end of the dataset I am
getting all the rows back, can you tell me where I am doing this wrong ???

5581  data aa_table2;
5582  retain col1 actpat_sum section count ;
5583      set aa_table1;
5584  by section ;
5585  if first.section then actpat_sum = . ;
5586  run;

NOTE: There were 67 observations read from the data set WORK.AA_TABLE1.
NOTE: The data set WORK.AA_TABLE2 has 67 observations and 6 variables.
NOTE: DATA statement used:
      real time           0.00 seconds
      cpu time            0.00 seconds


5587  proc sql noprint ;
5588    create table aa_table3 as
5589  select * , sum(actpat_sum)/%eval(&NC)  as total, 1 as tot_row
5590  from aa_table2
5591  union
5592  select * ,  0 as tot_row
5593  from aa_table2
5594  order by tot_row
5595  ;
WARNING: A table has been extended with null columns to perform the UNION
set operation.
NOTE: The query requires remerging summary statistics back with the original
data.
NOTE: Table WORK.AA_TABLE3 created, with 134 rows and 8 columns.

5596  quit;
NOTE: PROCEDURE SQL used:
      real time           0.03 seconds
      cpu time            0.01 seconds