LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (November 2001, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: Eden Don <edendon@263.NET>
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


Back to: Top of message | Previous page | Main SAS-L page