Date:         Wed, 15 Sep 2010 12:08:56 -0400
Reply-To:     Chang Chung <chang_y_chung@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Chang Chung <chang_y_chung@HOTMAIL.COM>
Subject:      Re: Recoding/Counting in PROC SQL
Comments: To: Craig Johnson <cjohns38@GMAIL.COM>

On Wed, 15 Sep 2010 10:42:04 -0500, Craig Johnson <cjohns38@GMAIL.COM>
wrote:
...
>I have an assessment that can be scored two different ways, the first way
is
>that items are scored 0-3 and all items are summed into a raw score so we
>can look up t-scores.  The second scoring method is to create a symptom
>count.  If the person scores a 2 or a 3 on the inventory they get a 1 and
>you sum across all the items.
...
sql loves data in the "long" format. hth.

proc sql;
  /* test data */
  create table one
  (id num, var1 num, var2 num, var3 num);
  insert into one
  values(1, 0, 1, 2)
  values(2, 3, 3, 3)
  values(3, 4, 4, 4)
  values(4, 4, 3, 4);

  /* wide to long */
  create table long as
  select id, 1 as var, var1 as value from one
  union
  select id, 2 as var, var2 as value from one
  union
  select id, 3 as var, var3 as value from one;

  /* a helper */
  create table range
  (minVal num, maxVal num);
  insert into range
  values(2, 3);

  /* calc sum of vars in two ways */
  create table two as
  select id, sum(value) as simple,
         sum(minVal <= value and value <= maxVal)
         as symptom
  from   long, range
  group by id
  order by id;

  /* check */
  select * from two;
  /* on lst
        id    simple   symptom
  ----------------------------
         1         3         1
         2         9         3
         3        12         0
         4        11         1
  */
quit;