Date:         Tue, 14 Sep 2004 12:57:28 -0700
Reply-To:     "Terjeson, Mark" <TERJEM@DSHS.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <TERJEM@DSHS.WA.GOV>
Subject:      Re: Simplifying proc sql
Comments: To: Pat Moore <anpam@UAA.ALASKA.EDU>
Content-Type: text/plain

Hi Pat,

Here's one of several approaches:


proc sql;
  create table MyResult as
  select
           id,
           sum(case
                   when complete eq 'Attrition'
                   then credits
                   else 0
               end)/sum(credits) as attritpct
  from
           MyData
  group by
           id
  ;
quit;



Hope this is helpful,
Mark Terjeson
Reporting, Analysis, and Procurement Section
Information Services Division
Department of Social and Health Services
State of Washington
mailto:terjem@dshs.wa.gov









-----Original Message-----
From: Pat Moore [mailto:anpam@UAA.ALASKA.EDU]
Sent: Tuesday, September 14, 2004 12:34 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Simplifying proc sql

SAS Gurus,
I wrote the following clumsy proc sql code to create an index of attrition
versus credits taken.  In the dataset, I have columns of ids, the numbers
of credits they took, and whether they dropped the course (complete
= "Attrition').
The steps work to do what I want, but I think it should be possible to
write this step much more simply. I'm still stumbling around in proc sql.
Can anyone help me clean this up? I need to insert this snippet in a
longer sql query that picks up a number of other variables from a couple
of files for further analysis.

proc sql;
 create table attrit as
 select
  unique id,
  sum(credits) as attrit
 from data
 where complete='Attrition'
 group by id;
 create table credits as
 select
  unique pidm,
  sum(credits) as credits
 from data
 group by id;
 create table attritpct as
 select
  b.id,
  attrit/credits as x,
  case
   when calculated x =. then 0
   else calculated x
  end as attritpct
 from attrit a right join credits b
 on a.id=b.id;

Pat Moore