Date: Fri, 25 Apr 2008 11:34:43 -0700
Reply-To: irinfigvam@yahoo.com
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Irin later <irinfigvam@YAHOO.COM>
Subject: Re: How to incorporate/assign variables within SQL query?
Desperately need help!
In-Reply-To: <01ac01c8a6f1$96e04b30$832fa8c0@HP82083701405>
Content-Type: text/html; charset=us-ascii
<table cellspacing='0' cellpadding='0' border='0' ><tr><td style='font: inherit;'><P>Mary & Sig,</P>
<P> </P>
<P>Now , although it looks like it passed through syntax, error log generated the following error about Correlated reference within subquery. What can be wrong? Could you ,please , give me a hand? This code definetely will kill me before the weekend!!::(</P>
<P> </P>
<P> </P>
<P>13 PROC SQL;<BR>13 ! <BR>14 <BR>15 CREATE TABLE ADM_OUT.enroll_memid_two_ssn_n4 AS<BR>16 (SELECT DISTINCT e.mem_num, e.mem_soc_sec_num, e.mem_birth_dt,e.mem_gender, d.mem_first_name,<BR>16 ! d.mem_last_name,d.mem_zip_cd,<BR>17 m.COV_EFF_DT ,m.COV_TERM_DT, p.model<BR>18 FROM ADM_OUT.smalltable c<BR>19 INNER JOIN (select<BR>19 !
TRIM(FIRST_NAME)||TRIM(PUT(YEAR(e.MEM_BIRTH_DT),2.))||TRIM(PUT(MONTH(e.MEM_BIRTH_DT),2.))||TRIM(PUT(DAY(e.MEM_BIRTH_DT),2<BR>19 ! .))||TRIM(e.MEM_GENDER) <BR>20 as MEM_ID2_,* <BR>21 from DW.ENROL_MEMBER_PROFILE) as e <BR>22 ON c.MEM_ID2_=e.MEM_ID2_<BR>23 INNER JOIN DW.ENROL_MEMBER_DEMO d <BR>24 ON
d.mem_num=e.mem_num<BR>25 AND <BR>26 FIRST_NAME=SUBSTR(d.mem_FIRST_NAME,1,5) <BR>27 INNER JOIN DW.MEM_MTH_EXP m ON m.MEM_PROF_KEY=e.MEM_PROF_KEY<BR>28 INNER JOIN DW.ENROL_STD_PRODUCT_PROFILE p ON p.STD_PRODUCT_KEY=m.STD_PRODUCT_KEY<BR>29 WHERE m.COV_EFF_DT le '01DEC04'd AND m.COV_TERM_DT Ge '1JAN04'd <BR>30 );<BR>INFO: Data set
ADM_OUT.smalltable.DATA is in a foreign host format. Cross Environment Data Access will be used, which may <BR>require additional CPU resources and reduce performance.<BR>ERROR: Correlated reference to column FIRST_name is not contained within a subquery.ERROR: Correlated reference to column FIRST_name is not contained within a subquery.ERROR: Correlated reference to column FIRST_name is not contained within a subquery.<BR>ERROR: Correlated reference to column MEM_BIRTH_DT is not contained within a subquery.ERROR: Correlated reference to column MEM_BIRTH_DT is not contained within a subquery.ERROR: Correlated reference to column MEM_BIRTH_DT is not contained within a subquery.<BR>ERROR: Correlated reference to column MEM_BIRTH_DT is not contained within a subquery.ERROR: Correlated reference to column MEM_BIRTH_DT is not contained within a subquery.ERROR: Correlated reference to column MEM_BIRTH_DT is not contained within a subquery.<BR>ERROR:
Correlated reference to column MEM_BIRTH_DT is not contained within a subquery.ERROR: Correlated reference to column MEM_BIRTH_DT is not contained within a subquery.ERROR: Correlated reference to column MEM_BIRTH_DT is not contained within a subquery.<BR>ERROR: Correlated reference to column MEM_GENDER is not contained within a subquery.ERROR: Correlated reference to column MEM_GENDER is not contained within a subquery.ERROR: Correlated reference to column MEM_GENDER is not contained within a subquery.<BR>NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.<BR><BR>--- On <B>Fri, 4/25/08, Mary <I><mlhoward@AVALON.NET></I></B> wrote:<BR></P>
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: rgb(16,16,255) 2px solid">From: Mary <mlhoward@AVALON.NET><BR>Subject: Re: How to incorporate/assign variables within SQL query? Desperately need help!<BR>To: SAS-L@LISTSERV.UGA.EDU<BR>Date: Friday, April 25, 2008, 12:29 PM<BR><BR><PRE>No, your as should be a new variable name; that's the result of your select,
and since you are concatenating, the result is one variable. You do need to
use the put function around your numeric variables, though:
trim(put(dob_year,4.))
-Mary
----- Original Message -----
From: Irin later
To: SAS-L@LISTSERV.UGA.EDU
Sent: Friday, April 25, 2008 11:10 AM
Subject: Re: How to incorporate/assign variables within SQL query?
Desperately need help!
Sig, I probably did not have this problem with a small table as at thAt
time I created mem_id2 it based on the big string ID
I am not sure ,however, what you mean saying "need to include ,*
like that?
INNER JOIN (select
TRIM(FIRST_NAME)||TRIM(DOB_YEAR)||TRIM(DOB_MONTH)||TRIM(DOB_DAY)||TRIM(MEM_GENDER))
as MEM_ID2_.*
--- On Fri, 4/25/08, Sigurd Hermansen <HERMANS1@WESTAT.com>
wrote:
From: Sigurd Hermansen <HERMANS1@WESTAT.com>
Subject: RE: How to incorporate/assign variables within SQL query?
Desperately need help!
To: irinfigvam@yahoo.com, SAS-L@LISTSERV.UGA.EDU
Date: Friday, April 25, 2008, 11:47 AM
Irin:
Mary has astutely identified a potential syntax problem with the data
type of DOB_YEAR. A TRIM() function applied to a SAS numeric type or RDBMS date
type will throw a syntax error.
But that makes me wonder why you didn't run into the same problem
when you created the MEM_ID2_ key attribute in adm_OUT.libinghospic...
You will also need to include ,* after as MEM_ID2_ to have access to
the other attributes of the SW table.
S
-----Original Message-----
From: Irin later [mailto:irinfigvam@yahoo.com]
Sent: Friday, April 25, 2008 11:13 AM
To: SAS-L@LISTSERV.UGA.EDU; Sigurd Hermansen
Subject: RE: How to incorporate/assign variables within SQL query?
Desperately need help!
Sig,
Now it looks like "as" is sepated by blank .
Subquery is enclosed within parentheses. At this time I put TRIM for Gender
part as well. Now I have 5 inner pairs of parentheses (for YEAR, MONTH, DAY,
GENDER , FIRST NAME) and one OUT pair for select statement after INNER JOIN.
However SQL SAS has same concern about the syntax.....Why?
What is wrong with a syntax?
CREATE TABLE out.enroll_memid_two_ssn_n4 AS
16 (SELECT DISTINCT e.mem_num, e.mem_soc_sec,
e.mem_birth_dt,e.mem_gender, d.mem_first_name,
16 ! d.mem_last_name,e.mem_zip_code,
17 m.COV_EFF_DT ,m.COV_TERM_DT , p.model
18 FROM adm_OUT.libinghospice c
19 INNER JOIN (select
TRIM(FIRST_NAME)||TRIM(DOB_YEAR)||TRIM(DOB_MONTH)||TRIM(DOB_DAY)||TRIM(MEM_GENDER))
as MEM_ID2_
_
_
_
22
22
22
76
76
76
ERROR 22-322: Syntax error, expecting one of the following: a
quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>,
=, >, >=, ERROR 22-322: Syntax error, expecting one of the following: a
quoted string, !, !!, &, *, **, +, ',', -, /, <, <=,
<>, =, >, >=, ERROR 22-322: Syntax error, expecting one of the
following: a quoted string, !, !!, &, *, **, +, ',', -, /, <,
<=, <>, =, >, >=,
?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT,
FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, ?,
AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN,
INFORMAT, INTO, IS, LABEL, LE, LEN, ?, AND, AS, BETWEEN,
CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS,
LABEL, LE, LEN,
LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT,
NOTIN, OR, ^, ^=, |, ||, ~, ~=. LENGTH, LET, LIKE, LT, LTT, NE,
NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. LENGTH, LET, LIKE, LT,
LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.
--- On Fri, 4/25/08, Sigurd Hermansen
<HERMANS1@WESTAT.com> wrote:
From: Sigurd Hermansen <HERMANS1@WESTAT.com>
Subject: RE: How to incorporate/assign variables within SQL
query? Desperately need help!
To: irinfigvam@yahoo.com, SAS-L@LISTSERV.UGA.EDU
Date: Friday, April 25, 2008, 10:17 AM
Irin:
The in-line or subquery has to be enclosed within
parentheses and the AS operator should be separated by blanks from the subquery
and alias:
..||MEM_GENDER) ) as MEM_ID2_
S
-----Original Message-----
From: Irin later [mailto:irinfigvam@yahoo.com]
Sent: Friday, April 25, 2008 9:52 AM
To: SAS-L@LISTSERV.UGA.EDU; Sigurd Hermansen
Subject: Re: How to incorporate/assign variables within
SQL query? Desperately need help!
Sig,thank you so much for the response!
According to Error log SQL does not accepts the
syntax below in the error log... I tried to take of "as" operator
but I got the same error message .
What is the matter? How can I try to fix it?
Irin
15 CREATE TABLE
adm_out.enroll_memid_two_ssn_n4 AS
16 (SELECT DISTINCT e.mem_num,
e.mem_soc_sec, e.mem_birth_dt,e.mem_gender, d.mem_first_name,
16 ! d.mem_last_name,e.mem_zip_code,
17 m.COV_EFF_DT ,m.COV_TERM_DT , p.model
18 FROM adm_OUT.samlltable c
19 INNER JOIN (select
TRIM(FIRST_NAME)||TRIM(DOB_YEAR)||TRIM(DOB_MONTH)||TRIM(DOB_DAY)||MEM_GENDER)as
MEM_ID2_
_
_
_
79
79
79
76
76
76
ERROR 79-322: Expecting a (.ERROR 79-322: Expecting
a (.ERROR 79-322: Expecting a (.
ERROR 76-322: Syntax error, statement will be
ignored.ERROR 76-322: Syntax error, statement will be ignored.ERROR 76-322:
Syntax error, statement will be ignored.
--- On Thu, 4/24/08, Sigurd Hermansen
<HERMANS1@WESTAT.COM> wrote:
From: Sigurd Hermansen
<HERMANS1@WESTAT.COM>
Subject: Re: How to incorporate/assign variables
within SQL query? Desperately need help!
To: SAS-L@LISTSERV.UGA.EDU
Date: Thursday, April 24, 2008, 4:59 PM
Irin:
It will help us (and you) if you will reduce the query to the elements
in question and test that first with small versions of the DW tables. I
cannot evaluate fully the syntax and attribute references without access
to the DW (which I don't want to have). Essentially you are using a
transitive join chain to execute a number of joins in sequence. While
SAS SQL may (or may not) promote the final WHERE clause, it will likely
not index the key attribute that you are creating unless you define that
key attribute in a subquery. You can test each join in sequence in a
transitive join. I'd encourage you to do that with small datasets before
relying on the query. Constraints on key values in the DW tables have a
lot to do with whether or not the transitive joins make sense.
I've inserted the definition of the key attribute (below). I haven't
tested that or reviewed the rest of the query.
S
PROC SQL;
CREATE TABLE out.enroll_memid_two_ssn_n4 AS
(SELECT DISTINCT e.mem_num, e.mem_soc_sec, e.mem_birth_dt,e.mem_gender,
d.mem_first_name, d.mem_last_name,e.mem_zip_code, m.COV_EFF_DT
,m.COV_TERM_DT , p.model
FROM OUT.smalltable c
INNER JOIN (select
TRIM(FIRST_NAME)||TRIM(DOB_YEAR)||TRIM(DOB_MONTH)||TRIM(DOB_DAY)||MEM_GE
NDER)as MEM_ID2_ from DW.MEMBER_PROFILE) as e
on c.MEM_ID2_ = e.MEM_ID2_
inner join DW.MEMBER_DEMO d
on d.mem_num=e.mem_num
AND
DOB_YEAR=YEAR(e.MEM_BIRTH_DT) AND
DOB_MONTH=PUT(e.MONTH(MEM_BIRTH_DT),Z2.) AND
DOB_DAY
=PUT(DAY(e.MEM_BIRTH_DT),Z2.) AND
FIRST_NAME=SUBSTR(d.mem_FIRST_NAME,1,5)) as k
INNER JOIN DW.MEM_MTH m ON m.MEM_PROF_KEY=e.MEM_PROF_KEY
INNER JOIN DW.PRODUCT_PROFILE p ON p.STD_PRODUCT_KEY=m.STD_PRODUCT_KEY
WHERE m.COV_EFF_DT le '01DEC04'd AND m.COV_TERM_DT Ge '1JAN04'd
);quit;
-----Original Message-----
From: owner-sas-l@listserv.uga.edu
[mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Irin later
Sent: Thursday, April 24, 2008 3:33 PM
To: SAS-L@LISTSERV.UGA.EDU; Sigurd Hermansen
Subject: Re: How to incorporate/assign variables within SQL
query? Desperately need help!
Sig, thank you very much for your help. It looks like I need this sub
query based on TWO tables Member_profile and member_demo (for the First
Name)
I am not sure if I use aliases properly in such a case . Also I wonder
if SQL accepts a kind of formatting like The following?
DOB_MONTH=PUT(e.MONTH(MEM_BIRTH_DT),Z2.)
Does this code below looks reasonable? What do you think? I am not sure
if I used allieaes "e " & "d" & "k"
properly within the subquery:
PROC SQL;
CREATE TABLE out.enroll_memid_two_ssn_n4 AS
(SELECT DISTINCT e.mem_num, e.mem_soc_sec, e.mem_birth_dt,e.mem_gender,
d.mem_first_name, d.mem_last_name,e.mem_zip_code, m.COV_EFF_DT
,m.COV_TERM_DT , p.model
FROM OUT.smalltable c
INNER JOIN (select
TRIM(FIRST_NAME)||TRIM(DOB_YEAR)||TRIM(DOB_MONTH)||TRIM(DOB_DAY)||MEM_GE
NDER)as MEM_ID2_ from DW.MEMBER_PROFILE e, DW.MEMBER_DEMO d where
d.mem_num=e.mem_num AND
DOB_YEAR=YEAR(e.MEM_BIRTH_DT)AND
DOB_MONTH=PUT(e.MONTH(MEM_BIRTH_DT),Z2.) AND
DOB_DAY
=PUT(DAY(e.MEM_BIRTH_DT),Z2.)AND
FIRST_NAME=SUBSTR(d.mem_FIRST_NAME,1,5)) as k ON k.MEM_ID2_=c.MEM_ID2_
INNER JOIN DW.MEM_MTH m ON m.MEM_PROF_KEY=e.MEM_PROF_KEY
INNER JOIN DW.PRODUCT_PROFILE p ON p.STD_PRODUCT_KEY=m.STD_PRODUCT_KEY
WHERE m.COV_EFF_DT le '01DEC04'd AND m.COV_TERM_DT Ge '1JAN04'd
);quit;
--- On Thu, 4/24/08, Sigurd Hermansen <HERMANS1@WESTAT.COM> wrote:
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: How to incorporate/assign variables within SQL
query? Desperately need help!
To: SAS-L@LISTSERV.UGA.EDU
Date: Thursday, April 24, 2008, 11:46 AM
Irin:
First I should mention that you have omitted an "ON"
operator.
The
query
will trigger a syntax error.
Provided that you have previously created the MEM_ID2_ "key"
attribute
in smalltable, SAS SQL will likely create a hash index
dynamically and
use it to filter tuples in the data warehouse tables. Defining
the key
in "in-line" views should not affect query performance that
much.
Simply
include the definition as
... INNER JOIN ( select
TRIM(FIRST_NAME)||TRIM(DOB_YEAR)||TRIM(DOB_MONTH)||TRIM(DOB_DAY)||MEM_GE
NDER as MEM_ID2_,* FROM DW.MEMBER_PROFILE ) as e ON ...
The inner queries execute first, so the newly defined key value
will be
available for use in an equivalence condition in the out query.
Try it
on a small scale version of a DW table to make sure that you
have the
syntax correctly specified.
S
-----Original Message-----
From: owner-sas-l@listserv.uga.edu
[mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Irin later
Sent: Thursday, April 24, 2008 10:43 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: How to incorporate/assign variables within SQL
query?
Desperately need help!
I have to create a query based on huge tables in Data warehouse
and
based on a small table which I want to merge with other DW
tables in
the query through composed MEM_ID2_.
PROC SQL;
CREATE TABLE out.enroll_memid_two_ssn_n4 AS
(SELECT DISTINCT e.mem_num, e.mem_soc_sec,
e.mem_birth_dt,e.mem_gender,
d.mem_first_name, d.mem_last_name, e.mem_zip_code, p.model
FROM smalltable c
INNER JOIN DW.MEMBER_PROFILE e ON MEM_ID2_=C. MEM_ID2_
INNER JOIN DW.MEMBER_DEMO d.mem_num=e.mem_num
INNER JOIN DW.MEM_MTH m ON m.MEM_PROF_KEY=e.MEM_PROF_KEY
INNER JOIN DW.PRODUCT_PROFILE p ON
p.STD_PRODUCT_KEY=m.STD_PRODUCT_KEY
WHERE m.COV_EFF_DT le '01DEC04'd AND m.COV_TERM_DT Ge
'1JAN04'd
);quit;
I can merge them just through MEM_ID2_ !!!!which I created in
the small
table.
I should create the same MEM_ID2_ in my query but I have NO IDEA
how to
incorporate the following into this query?
I assume I should create variable/S first....Is it possible in
the
select query?
How to do it?
LENGTH DOB_YEAR $ 4 DOB_MONTH $ 2 MEM_ID2_ $ 50
mem_FIRST_NAME $ 8 mem_LAST_NAME $ 12;
DOB_YEAR=YEAR(MEM_BIRTH_DT);
DOB_MONTH=PUT(MONTH(MEM_BIRTH_DT),Z2.);
DOB_DAY =PUT(DAY(MEM_BIRTH_DT),Z2.);
FIRST_NAME=SUBSTR(mem_FIRST_NAME,1,5);
MEM_ID2_=TRIM(FIRST_NAME)||TRIM(DOB_YEAR)||TRIM(DOB_MONTH)||TRIM(DOB_DAY
)||MEM_GENDER;
Could you please kindly help me with it?
Irin
________________________________
Be a better friend, newshound, and know-it-all with
Yahoo!
Mobile. Try it now.
<http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i6
2sR8HDtDypao8Wcj9tAcJ>
________________________________
Be a better friend, newshound, and know-it-all with Yahoo!
Mobile. Try it now.
<http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i6
2sR8HDtDypao8Wcj9tAcJ>
----------------------------------------------------------
Be a better friend, newshound, and know-it-all with
Yahoo! Mobile. Try it now.
--------------------------------------------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile.
Try it now.
------------------------------------------------------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it
now.</PRE></BLOCKQUOTE></td></tr></table><br>
<hr size=1>Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. <a href="http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ "> Try it now.</a>
| 
