Date: Mon, 19 Nov 2001 11:10:47 -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
Content-Type: text/plain
To make it more flexible, i.e.. when you do NOT
know the types beforehand, an extra sql step is needed
to find out all the types and construct a macro var
which is a list of all the types, it also save some
keystroke when there are many types:
data xx;
input date $ type $ weight;
cards;
200110 0020 100
200110 0030 200
200110 0040 300
200110 1099 999
200111 0020 101
200111 0030 201
200111 0040 301
200111 8888 888
;
proc sql noprint;
select distinct
'max(case when type="'||trim(left(type))
||'" then weight else . end) as w'
||left(put(input(type,best.),best.))
into : typelist separated by ','
from xx;
create view yy as
select distinct date,
&typelist
from xx
group by date
;
options nocenter;
proc print data=yy;
run;
------------------------
Obs date w20 w30 w40 w1099 w8888
1 200110 100 200 300 999 .
2 200111 101 201 301 . 888
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
