PROC SQL : error: ERROR: Syntax error at or near ".T"

[Aexor]

Hi ,

I need suggestion for the error I am getting while creating table.

Since I have libray refernce having more than  8 char. So I created a libref 

like this 

libname CAS_LIB  cas caslib="&mp_sourceName";

but whenever I am trying to create table using CAS_LIB as library . It is throwing error.

I have also inserted code for ref

      
       proc casutil;
       load data =planning.ed_&inputTable CASOUT="ed_&inputTable" 
       OUTCASLIB="&mp_sourceName" replace;	   
	   
	   libname &vf_lib cas caslib="&mp_sourceName";

 
        proc fedsql SESSREF=&_SESSREF_;
            create table &vf_lib..tmp_&inputTable. as 
             select a.* ,b.time_id as start_dt_sk
             from 
                &vf_lib..&inputTable.  a, &vf_lib..ed_&inputTable  b  
            where 
                a.%bquote(&time_dim.) between b.start_date and b.end_date;
         quit;

    proc cas;
            sessionProp.setsessopt / caslib="&mp_sourceName";
             table.alterTable / columns={{rename="%TRIM(&prd_var)", 
    name="actual"},
                  {rename="%sysfunc(catx(_,&prd_var.,fcst))", name="predict"}}
              name="tmp_&inputTable.";
          quit;

/* This mp_source name have values like CASUSER(CMSTEST), ShoppingInsight  , ModelValuation , etc */ 

ernce.

normal: MPRINT(X): proc casutil;
note: NOTE: The UUID '3887c308-3320-8d4d-81f1-b24a9322bcac' is connected using session CASAUTO.
normal: SYMBOLGEN: Macro variable PLAN_LIB resolves to planning
normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102
normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102
normal: SYMBOLGEN: && resolves to &.
normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest)
normal: MPRINT(X): load data =planning.ed_OUTFOR_102 CASOUT="ed_OUTFOR_102" OUTCASLIB="CASUSER(cmstest)" replace;
note: NOTE: PLANNING.ED_OUTFOR_102 was successfully added to the "CASUSER(cmstest)" caslib as "ED_OUTFOR_102".
normal: SYMBOLGEN: Macro variable VF_LIB resolves to CAS_LIB
normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest)
normal: MPRINT(NPF_INPUT_CDL_FACT): libname CAS_LIB cas caslib="CASUSER(cmstest)";
note: NOTE: Libref CAS_LIB was successfully assigned as follows:
note: Engine: CAS
note: Physical Name: 3887c308-3320-8d4d-81f1-b24a9322bcac
note: NOTE: PROCEDURE CASUTIL used (Total process time):
note: real time 0.28 seconds
note: cpu time 0.05 seconds
note:
note:
normal: SYMBOLGEN: Macro variable _SESSREF_ resolves to CASAUTO
normal: MPRINT(X): proc fedsql SESSREF=CASAUTO;
normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest)
normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102
title: 17 The SAS System Wednesday, February 22, 2023 05:25:00 PM
title:
normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest)
normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102
normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest)
normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102
normal: SYMBOLGEN: Macro variable TIME_DIM resolves to start_date
normal: MPRINT(X): create table CASUSER(cmstest).tmp_OUTFOR_102 as select a.* ,b.time_id as start_dt_sk from
normal: CASUSER(cmstest).OUTFOR_102 a, CASUSER(cpstest).ed_OUTFOR_102 b where a.start_date between b.start_date and b.end_date;
error: ERROR: Syntax error at or near ".T"

[LinusH]

Maybe it's about my ignorance, but I don't recognise this syntax:

create table CASUSER(cmstest).tmp_OUTFOR_102

[Quentin]

I suggest trying to replicate the problem with a much simpler sample bit of code. In particular, replace all the macro variable references with hard code.    If the PROC CAS step is not needed to produce the error, remove it.

It looks to me like your macro variable VF_LIB is resolving to CASUSER(cmstest), I think you expect it to resolve to  CAS_LIB.  

I don't know CAS or FEDSQL  Is PROC FEDSQL actually executing on a different SAS session?  (I see SESSREF=).  Could the macro variable VF_LIB  have a different value in that session?

[Aexor]

Thank you for your suggestion. I will try this.

VF_LIB = CAS_LIB throughout the session .
I have used CAS_LIB as libname to avoid using libref which is more than 8 char .

I want tables to be created in CAS_LIB and I want those tables to get accessed in later steps to which is not happening.

if you check the attached code . I have mentioned the code steps .

[Quentin]

Yes, I see the code steps, but the MPRINT output is confusing to me.

It looks like this code:

create table &vf_lib..tmp_&inputTable. as

is somehow resolving to:

normal: MPRINT(X): create table CASUSER(cmstest).tmp_OUTFOR_102 as

which doesn't make sense to me and shouldn't happen if the macro variable VF_LIB has the value CAS_LIB.  Unless it's possible that the MPRINT log you showed is not from the code you shared.

Thus my suggestion to replace the macro variables with the hard-coded code you are intending to generate, e.g.

create table cas_lib.tmp_OUTFOR_102 as

If that code works, then you know the problem is somewhere in your macro variable resolution.  And it it doesn't work, then it will be easier to debug without all the macro variables.

Common advice is to debug the SAS code first and get that working, then add macro complexity and debug that.

[Aexor]

I am getting this error . Thought in previous step CAS_LIB is working fine. am I missing anything here. Please help

proc fedsql ; create table CAS_LIB.tmp_OUTFOR_102 as select a.* ,b.time_id as start_dt_sk from CAS_LIB.OUTFOR_102
normal: a, CAS_LIB.ed_OUTFOR_102 b where a.start_date between b.start_date and b.end_date;
error: ERROR: Table "CAS_LIB.OUTFOR_102" does not exist or cannot be accessed
error: ERROR: BASE driver, schema name CAS_LIB was not found for this connection

[Quentin]

Sorry, I'm not a FEDSQL person or a CAS person, so I won't be able to help.  I'd suggest posting the full PROC FEDSQL step you are running (with no macro vars) and the full log.  Then maybe you'll get lucky and someone like @SASJedi will wander by and lend you a hand.

[Aexor]

Sorry , Can you please elaborate , What could be the correct syntax

[SASJedi]

1. When PROC FedSQL executes code on the SAS Compute Server (what you might call base SAS), it can ONLY access data from non-concatenated SAS libraries which are not associated with a caslib. When executing in CAS (as indicated by the presence of the SESSREF= option on the PROC FedSQL statement) it can ONLY access data in caslibs. 
2. When you are operating in a multi-user CAS session, every user has a caslib named CASUSER. In order to differentiate your CASUSER caslib from other users, the system identifies the caslib as CASUSER(userID).  So you need to use the actual CASLIB name for this, not the libref. Of course, the caslib name will seem inappropriate to the FedSQL compiler because of the included punctuation. 
3. To make the name work as is, you can add double quotes to indicate that the text is a valid name, something like this:

proc fedsql SESSREF=&_SESSREF_;
create table "&mp_sourceName"."tmp_&inputTable" as 
	select a.* 
		  ,b.time_id as start_dt_sk 
		from "&mp_sourceName"."&inputTable"  a 
			"&mp_sourceName"."ed_&inputTable" b 
		where a.%bquote(&time_dim.) between b.start_date and b.end_date
;
quit;

I hope this helps.

Mark