The SAS Output Delivery System and reporting techniques

Correlated reference

Reply
Contributor
Posts: 58

Correlated reference

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
SAS Super FREQ
Posts: 8,868

Re: Correlated reference

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
Super Contributor
Posts: 273

Re: Correlated reference

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
Frequent Contributor
Posts: 91

Re: Correlated reference

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
Ask a Question
Discussion stats
  • 3 replies
  • 989 views
  • 0 likes
  • 4 in conversation