Date:         Thu, 3 Jun 2004 22:49:49 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Proc SQL
Comments: To: Nicole Bibb <nicole.d.bibb@WELLSFARGO.COM>
Content-Type: text/plain; charset="iso-8859-1"

Nicole:
So far as I can see, you are making up your own SQL syntax:

group by type (score1-score4);

 ...very creative but not very effective. Remember that you are trying to
communicate with a machine that has no imagination or ability to interpret
your intent. To the SAS SQL compiler, this clause looks like a function
type() with an argument evaluated from the expression score1-score4. (SAS
SQLdoes not expand variable ranges.)

Data in different rows of the same dataset does not inhibit a solution. A
'reflexive' (self-join) of the form 'a inner join a' aligns two copies of
the same dataset and allows comparisons acrossrows.
Sig


-----Original Message-----
From: Nicole Bibb
To: SAS-L@LISTSERV.UGA.EDU
Sent: 6/3/2004 5:00 PM
Subject: Proc SQL

I need to run the following proc sql for the different groups described
below.

proc sql;
   create view worktst.gdval&date as
   select score1,score2,score3,score4,
          count(*) as noact&date format=comma12.0,
          sum(bcur) as osbal&date format=comma12.0,
          sum(acln) as clbal&date format=comma12.0,
          mean(util) as avg_util&date format=percent8.2,
          sum(bcur)/sum(acln) as tot_util&date format=percent8.2
     from work.mthbsl&date
     group by type (score1-score4);
  quit;

IF type =new or old then use score1 and score2
IF type = Stud,sec,core use score2 score3 score4

The problem is that all the data is in the same dataset. I really do not
want to to create multiple datasets because I will need to merge the
data
with other data later in the program.