Date:         Mon, 19 Nov 2001 10:50:18 -0500
Reply-To:     "Huang, Ya" <ya.huang@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <ya.huang@PFIZER.COM>
Subject:      Re: how to create a view using SQL
Comments: To: Eden Don <edendon@263.NET>
Content-Type: text/plain

Eden,

Assume you already know all the possible 'type',
then here is a brute force code:

data xx;
input date $ type $ weight;
cards;
200110      0020     100
200110      0030     200
200110      0040     300
200111      0020     101
200111      0030     201
200111      0040     301
;

proc sql;
create view yy as
select distinct date,
 max(case when type='0020' then weight else . end) as w20,
 max(case when type='0030' then weight else . end) as w30,
 max(case when type='0040' then weight else . end) as w40
from xx
group by date
;

proc print data=yy;
run;
----------------------------

Obs     date     w20    w30    w40

 1    200110    100    200    300
 2    200111    101    201    301

HTH

Ya Huang
-----Original Message-----
From: Eden Don [mailto:edendon@263.NET]
Sent: Monday, November 19, 2001 12:55 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: how to create a view using SQL


Dear all,

I'd like to create view using a single SAS SQL procedure. The data in
SAS data set is something like this,

date          type       weight
200110      0020     100
200110      0030     200
200110      0040     300
200111      0020     101
200111      0030     201
200111      0040     301

Can I create a view, from which I can see the following?

date         w20    w30   w40
200110     100    200    300
200111     101    201    301

Thanks very much in advance.


Eden