BookmarkSubscribeRSS Feed
lloraine
Calcite | Level 5
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
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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
Andre
Obsidian | Level 7
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
advoss
Quartz | Level 8
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2694 views
  • 0 likes
  • 4 in conversation