PROC DS2 ERROR: Syntax error at or near "CONTAINS"

[rajdeep]

Hi Folks,

Greetings of the day and happy new year to all.

I was trying to execute the below code

proc ds2;
	thread newton/overwrite=yes;
			method run();
			SET {Select distinct matsc.*,SPMF.Final
from Maturity_score matsc left
join OCCUPATION_XREF_MAP SPMF on 
strip(upcase(matsc.OCCUPATION_DESC)) contains strip(upcase(SPMF.OCCUPATION)) or strip(upcase(matsc.OCCUPATION_DESC)) contains strip(upcase(SPMF.STANDARD_PROFESSION))}; /* Ability to use FedSql query within DS2 */

				end;

	endthread;
data Employment_Score/overwrite=yes;
		dcl thread newton frac2; /* Declare an Instance of the newton thread */

		method run();
			set from frac2 threads=4; /* <--- You Change the number of threads to control the threaded reads */
		end;
	enddata;
run;
quit;

Log:

ERROR: Compilation error.
ERROR: Syntax error at or near "CONTAINS"
ERROR: Line 28: Unable to prepare SELECT statement for table {Select distinct matsc.*,SPMF.Final from Maturity_score matsc left
join OCCUPATION_XREF_MAP SPMF on strip(upcase(matsc.OCCUPATION_DESC)) contains strip(upcase(SPMF.OCCUPATION)) or
strip(upcase(matsc.OCCUPATION_DESC)) contains strip(upcase(SPMF.STANDARD_PROFESSION))} (rc=0x80fff802U).

The above code is running fine in normal Proc sql scope, but in proc Ds2 it's throwing error. Is it something like we can't use contains function in FedSql query inside proc DS2?

Please help. 

Thanks in advance.

[rajdeep]

Hi Folks,

Can someone please help me out...!!! There is not much relevant information I found related to FedSQL queries over forums, that's why I need some suggestions.

If anyone find any information related to FedSQL queries functions, please do share here.... 

Thanks in advance....

[rajdeep]

Hello Folks,

Please let me know if anyone having any suggestions or workarounds. I need the help here badly.. Thanks in advance....

[SASJedi · Accepted solution]

The problem that DS2 is integrated with FedSQL, not PROC SQL, and FedSQL is significantly more strict about ANSI expressions - it has very few SAS extensions, and the CONTAINS operator is one of these. Specifically:

on strip(upcase(matsc.OCCUPATION_DESC)) CONTAINS strip(upcase(SPMF.OCCUPATION)) 
or strip(upcase(matsc.OCCUPATION_DESC)) CONTAINS strip(upcase(SPMF.STANDARD_PROFESSION))

Instead of the SAS-specific CONTAINS operator, use the ANSI LIKE operator. Something like this should work for you:

thread newton/overwrite=yes;
   method run();
	   SET {Select distinct matsc.*,SPMF.Final
              from Maturity_score matsc 
              left join 
              OCCUPATION_XREF_MAP SPMF 
              on    strip(upcase(matsc.OCCUPATION_DESC)) like '%'||strip(upcase(SPMF.OCCUPATION))||'%' 
                 or strip(upcase(matsc.OCCUPATION_DESC)) like '%'||strip(upcase(SPMF.STANDARD_PROFESSION))||'%'
           }
; /* Ability to use FedSql query within DS2 */
end;
endthread;

[rajdeep]

Hi  SASJedi,

Thanks a lot for your reply. That worked.

Have a great day ahead.

[SASJedi]

Woot! We WIN 🙂 Have a great day 

Mark

[rajdeep]

HI SASJedi,

Thanks a lot for your response. I have also another thread in the Community regarding the use of INPUTN function in Fedsql query, can you please help me out how to use the same.

 The below is the scenario and it's log.

proc ds2; /*Score column mapping based on Postal code and FSA*/
	thread newton/overwrite=yes;
dcl double Level;
	  method convert(double level) returns double;
 set LIB_TS23.CIF_ACCOUNT_CURR;
 Level=inputn(CIFP_CUSTOM_DATA_81,8.);
 return Level;
 end;
		method run();
			SET {select distinct
      TRIM(LEFT(PUT(CIF_ACCOUNT_CURR.CIFP_ACCOUNT_ID5,BEST32.))) as MAST_ACCOUNT_ID
   from LIB_TS23.CIF_ACCOUNT_CURR as CIF_ACCOUNT_CURR 
inner join
      LIB_ADM.ADM_STD_DISP_USER_V as ADM_STD_DISP_USER_V
   on  ADM_STD_DISP_USER_V.APA_APP_NUM = INPUTN(CIF_ACCOUNT_CURR.CIFP_CUSTOM_DATA_81,8.)
      and CIF_ACCOUNT_CURR.CIFP_OFFICER_NBR = 'ADMACE'}; /* Ability to use FedSql query within DS2 */

				end;
	endthread;
data test/overwrite=yes;
		dcl thread newton frac1; /* Declare an Instance of the newton thread */
		method run();
			set from frac1 threads=4; /* <--- You Change the number of threads to control the threaded reads */
		end;
	enddata;
run;
quit;

Log:

ERROR: Compilation error.
ERROR: Function INPUT(VARCHAR, DOUBLE) does not exist
WARNING: No function matches the given name and argument types.
ERROR: Line 34: Unable to prepare SELECT statement for table {select distinct  
       TRIM(LEFT(PUT(CIF_ACCOUNT_CURR.CIFP_ACCOUNT_ID5,BEST32.))) as MAST_ACCOUNT_ID  from   LIB_TS23.CIF_ACCOUNT_CURR as 
       CIF_ACCOUNT_CURR  inner join  LIB_ADM.ADM_STD_DISP_USER_V as ADM_STD_DISP_USER_V  on   ADM_STD_DISP_USER_V.APA_APP_NUM 
       = INPUT(CIF_ACCOUNT_CURR.CIFP_CUSTOM_DATA_81,8.)  and CIF_ACCOUNT_CURR.CIFP_OFFICER_NBR = 'ADMACE'} (rc=0x80fff802U).
NOTE: PROC DS2 has set option NOEXEC and will continue to prepare statements.

 In the above code it's perfectly converting the CIFP_CUSTOM_DATA_81 column into Numeric variable, but in Fedsql I am not getting how to get rid of it. Infact I tried to use the Level variable inside Fedsql, but it says Level column not found.

In the select you can see there is a PUT statement and TRIM which is working fine, but the where clause INPUTN function throwing the error.

You can replicate and try. 

Thanks in advance.

[SASJedi]

When troubleshooting FedSQL embedded in DS2, it is often helpful to test your FedSQL query using PROC FedSQL first. 

It appears that you did no supply single quotes surrounding the informat name, as is required for the INPUTN function. See the example in the for the FedSQL INPUTN function documentation.

All the best,

Mark