Date:         Thu, 2 May 1996 13:29:05 EST
Reply-To:     Cynthia_Stetz_at_NJQA2@PCMAILGW.ML.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Cynthia Stetz <Cynthia_Stetz_at_NJQA2@PCMAILGW.ML.COM>
Subject:      Re: proc sql question - HELP !!
Comments: To: Mr B J Chambers <bjc22940@GGR.CO.UK>

I'm no expert, but it seems to me that the only variable you specified in an
'AS' clause (VSDT) is named correctly, so I would just add 'AS' clauses to the
other two variables.  Am I missing something?
Cynthia
______________________________ Reply Separator _________________________________
Subject: proc sql question - HELP !!
Author:  Mr B J Chambers <bjc22940@GGR.CO.UK> at UNIXGTWY
Date:    5/2/96 1:12 PM
 
 
Hi there SAS-lers,
 
 I am trying to combine data from 2 ORACLE databases where there are no
database links set-up so I need to make a second connection in proc sql. I
am sure I have the syntax of my query perfect but when I try and run it, it
gives me the following error:
 
NOTE: The SAS System stopped processing this step because of errors.
 
42   proc contents data=combview.adver;
WARNING: The SQL View COMBVIEW.ADVER contains un-named expressions in its
select list. SQL will generate internal names using the template _TEMVnnn.
43   run;
 
 A proc contents gives the following :
 
    -----Alphabetic List of Variables and A
 
#    Variable    Type    Len    Pos    Form
-------------------------------------------
3    VSDT        Num       8     16    DATE
1    _TEMA002    Char      5      0
2    _TEMA004    Char      8      5
 
 The formats are fine, the only problem is the variable name. Any help from
a proc sql expert (Rick Kent et el. ?) would be sooo appreciated ! Any
responses by personal mail please (bjc22940@ggr.co.uk) and to the list as I
may miss a reply in a digest. Here is the code I am piloting :
 
proc sql;
connect to oracle as orac1
   (user=&orauser orapw=&orapw
    PATH='@PATH1' buffsize=500);
connect to oracle as orac2
   (user=&orauser2 orapw=&orapw2
    PATH='@PATH2' buffsize=500);
create view combview.adver as
   (select UPCASE(PJID) label='Project ID ',
   UPCASE(PTID) label='Protocol ID ',
   DATEPART(VSDT ) as VSDT FORMAT = DATE7. label='Visit date '
   from connection to orac1
   (select * from POBIN009.ae_2008
   where PTID= 'B2008' ))
UNION
   (select UPCASE(PJID) label='Project ID ',
   UPCASE(PTID) label='Protocol ID ',
   DATEPART(VSDT ) as VSDT FORMAT = DATE7. label='Visit date '
 from connection to orac2
   (select * from POAIN009.ae_2008
   where PTID= 'A2008' )) ;
quit;run;
 
 Thanks in advance,
 
Bruce in London