Date:         Sun, 23 Oct 2005 11:39:23 -0400
Reply-To:     Ya Huang <ya.huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AMYLIN.COM>
Subject:      Re: Data Transformation Help

If a,b,c,d can only take value of 1-6, then this simple wallpaper
code will do it:

data xx;
input id a b c d;
cards;
1 2 3 5 1
1 5 6 5 3
1 2 4 2 4
2 1 3 4 5
2 5 6 2 3
2 4 5 2 1
;

proc sql;
select distinct id,
 sum(sum(a=1),sum(b=1),sum(c=1),sum(d=1)) as v1,
 sum(sum(a=2),sum(b=2),sum(c=2),sum(d=2)) as v2,
 sum(sum(a=3),sum(b=3),sum(c=3),sum(d=3)) as v3,
 sum(sum(a=4),sum(b=4),sum(c=4),sum(d=4)) as v4,
 sum(sum(a=5),sum(b=5),sum(c=5),sum(d=5)) as v5,
 sum(sum(a=6),sum(b=6),sum(c=6),sum(d=6)) as v6
from xx
group by id
order by id
;


 id   v1   v2   v3   v4   v5   v6
----------------------------------------
  1    1    3    2    2    3    1
  2    2    2    2    2    3    1


Kind regards,

Ya Huang

On Sun, 23 Oct 2005 06:38:54 -0700, Zai Saki <zaisaki@GMAIL.COM> wrote:

>Hi All,
>I have a data set with variables id, a, b, c and looks like:
>
>1 2 3 5 1
>1 5 6 5 3
>1 2 4 2 4
>2 1 3 4 5
>2 5 6 2 3
>2 4 5 2 1
>.
>.
>;
>
>I would like to summarize data for each id and create a new variable
>representing each code occuring in variables a, b and c.
>
>Essentially the new data set should have variables id, v1 - v6.
>v1 represents the number of times code 1 occurs in variables a, b and c
>in the original data set, and so on for v2 - v6.
>
>The data set would like;
>1 1 3 2 2 3 1
>2 2 2 2 2 1 1
>
>Your help is much appreciated.
>
>Thanks very much,
>Zai Saki