Correlated reference

lloraine — Tue, 15 Jan 2008 20:00:40 GMT

I am not sure where to post this question. If this is not the forum, could you tell me which one.

I am using Proc SQL. I have the following code. I have remmed out the sections that are giving me the error with /* */. When I put these sections back into the code, I get "Correlated Reference to Column GP_N is not contained within a subquery.

I know it has something to do with the syntax but I am not sure how to correct it. I remmed out the code relating to table GroupCLH since that is what is giving me the problem. I am doing outer joins and I have a one to one join for the GroupCLH table. It doesn't like where I put it in the from statement.
The code is:

PROC SQL;
create table LINDA.LifestyleReturns as
Select
distinct
VIEWLIFESTYLEREPORT.GP_ID,
VIEWLIFESTYLEREPORT.CL_N,
VIEWLIFESTYLEREPORT.GP_N,
VIEWLIFESTYLEREPORT.GP_NM,
VIEWLIFESTYLEREPORT.GP_EFF_FRM_DT,
VIEWLIFESTYLEREPORT.GP_EFF_TO_DT,
VIEWLIFESTYLEREPORT.LSR_CL_IN,
VIEWLIFESTYLEREPORT.COLSV_DESC_T,
VIEWLIFESTYLEREPORT.LSR_ATP_DESC,
VIEWLIFESTYLEREPORT.LSR_PART_DESC,
VIEWLIFESTYLEREPORT.LSR_RWD_METH,
VIEWLIFESTYLEREPORT.LSR_OTCM_PART,
VIEWLIFESTYLEGROUPS.GROUP_NUMBER,
VIEWLIFESTYLEGROUPS.LSR_EFF_FRM_DT,
VIEWLIFESTYLEGROUPS.LSR_EFF_TO_DT,
VIEWLIFESTYLEGROUPS.CONTRACT_BEGIN_DT,
VIEWLIFESTYLEGROUPS.CONTRACT_END_DT,
VIEWLIFESTYLEGROUPS.GROUP_MKT_STUS_C,
/*GROUPCLH.CL_ID,
GROUPCLH.CL_ANV_M,
GROUPCLH.PARTNERPLAN,
GROUPCLH.EPLATFORMIND,*/
CLIENTMGR.CL_ID,
CLIENTMGR.SD_N,
CLIENTMGR.SD_NM,
CLIENTMGR.SSSR_N,
CLIENTMGR.EM_LAST_NM,
CLIENTMGR.CSG,
ASM.CL_ID,
ASM.SSSR_N,
ASM.EM_LAST_NM,
VIEWLIFESTYLEWORKLOAD.GROUP_ID,
VIEWLIFESTYLEWORKLOAD.FA_TOTAL_CONTRACTS,
VIEWLIFESTYLEWORKLOAD.PRODUCT_DESCRIPTION,
GENERALADDRESS.CLID,
GENERALADDRESS.ADLINE1,
GENERALADDRESS.ADLINE2,
GENERALADDRESS.CITY,
GENERALADDRESS.ST,
GENERALADDRESS.ZIP,
VIEWLIFESTYLETYPES.GP_ID,
VIEWLIFESTYLETYPES.LSR_TYPE_DESC,
VIEWLIFESTYLEPROGRAMS.GPID,
VIEWLIFESTYLEPROGRAMS.LSRPGMNM,
VIEWLIFESTYLEPROGRAMS.LSRPGMTYP,
GRPASSOC.CLID,
GRPASSOC.ASSOCNUMBER,
GRPASSOC.ASSOCIATIONNM
From WORK.VIEWLIFESTYLEREPORT/*,WORK.GROUPCLH*/

Left join WORK.VIEWLIFESTYLEGROUPS
On VIEWLIFESTYLEREPORT.GP_N = VIEWLIFESTYLEGROUPS.GROUP_NUMBER

Left join WORK.CLIENTMGR
On VIEWLIFESTYLEREPORT.GP_ID = CLIENTMGR.CL_ID
Left join WORK.ASM
On VIEWLIFESTYLEREPORT.GP_ID = ASM.CL_ID
Left join WORK.VIEWLIFESTYLEWORKLOAD
On VIEWLIFESTYLEREPORT.GP_ID = VIEWLIFESTYLEWORKLOAD.GROUP_ID
Left join WORK.GENERALADDRESS
On VIEWLIFESTYLEREPORT.GP_ID = GENERALADDRESS.CLID
Left join WORK.VIEWLIFESTYLETYPES
On VIEWLIFESTYLEREPORT.GP_ID = VIEWLIFESTYLETYPES.GP_ID
Left join WORK.VIEWLIFESTYLEPROGRAMS
On VIEWLIFESTYLEREPORT.GP_ID = VIEWLIFESTYLEPROGRAMS.GPID
Left join WORK.GRPASSOC
On VIEWLIFESTYLEREPORT.GP_ID = GRPASSOC.CLID
/*Where VIEWLIFESTYLEREPORT.GP_ID = GROUPCLH.CL_ID*/
Where VIEWLIFESTYLEWORKLOAD.PRODUCT_DESCRIPTION <> 'Supplemental Only';

quit; Message was edited by: lloraine

Re: Correlated reference

Cynthia_sas — Wed, 16 Jan 2008 01:16:00 GMT

Hi:
This is not an ODS or Base Report procedure (PRINT, REPORT, TABULATE) question. There's not really a forum for SQL and for your code example, Tech Support is your best bet for detailed SQL help.

To send a question to Tech Support, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem" (under the heading for Support).

cynthia

Re: Correlated reference

Andre — Wed, 16 Jan 2008 08:46:35 GMT

May i add to the answer of Cynthia
that you may also ask this kind of question to the
sas users discussion list 'SAS-L'
as you were asking "could you tell me which one".

When i was searching on google group:
group:comp.soft-sys.sas author:Linda author:Rutge
i have seen none reference and it lead me to think
that perhaps you are not aware of its existence.

http://groups.google.fr/group/comp.soft-sys.sas/about

Of course, Technical sas support is the best official place
for having a solution upon a technical question
but in case of lack of practice and so one some sas users
may also provide quick 'unofficial' answer.

Cynthia may correct this sentence after.

Andre

Re: Correlated reference

advoss — Wed, 16 Jan 2008 13:22:34 GMT

Stick with the JOIN syntax and try something like
From WORK.VIEWLIFESTYLEREPORT
inner join WORK.GROUPCLH
on viewlifestylereport.common_key = groupclh.common_key
Left join WORK.VIEWLIFESTYLEGROUPS

topic Correlated reference in ODS and Base Reporting

Correlated reference

Re: Correlated reference

Re: Correlated reference

Re: Correlated reference