topic Re: Stucked with connection to SQLSVR in New SAS User

Stucked with connection to SQLSVR

Alex91 — Fri, 15 Mar 2019 10:24:47 GMT

Hi, im new one to SAS and stucked with such problem

I'm trying to run next code and getting error below.

proc sql;
CONNECT TO SQLSVR as con1
(Datasrc=CIDWH authdomain=MSSQLCIDB1);
CREATE TABLE &m_abt..&pre.SEASON as
select *
from connection to con1(
      select 
      case
          when month(RIRA_stg.TRANSDATE) in (12,1,2,) then 'Winter'
          when month(RIRA_stg.TRANSDATE) in (3,4,5) then 'Spring'
          when month(RIRA_stg.TRANSDATE) in (6,7,8) then 'Summer'
          when month(RIRA_stg.TRANSDATE) in (9,10,11) then 'Autumn'
      end 
      as SEASON
      FROM &m_dds..RAW_RETAIL_INTERNET_ABT_stg RIRA_stg      
      );
DISCONNECT FROM con1;
quit;

MPRINT(CREATE_TARGET_V2): CREATE TABLE ABT.ABT_31JAN18_SEASON as select * from connection to con1( select case when
month(RIRA_stg.TRANSDATE) in (12,1,2,) then 'Winter' when month(RIRA_stg.TRANSDATE) in (3,4,5) then 'Spring' when
month(RIRA_stg.TRANSDATE) in (6,7,8) then 'Summer' when month(RIRA_stg.TRANSDATE) in (9,10,11) then 'Autumn' end as
RIRA_stg.TRANSDATE FROM abt.RAW_RETAIL_INTERNET_ABT_stg RIRA_stg );
ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near ')'. : [SAS][ODBC
SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile errors.

What i supposed to do to fix this problem?

Re: Stucked with connection to SQLSVR

VDD — Fri, 15 Mar 2019 10:31:50 GMT

did you setup an ODBC connection?

Re: Stucked with connection to SQLSVR

Alex91 — Fri, 15 Mar 2019 11:00:34 GMT

Nope. I have a similar node with code like

and it works great

proc sql;
	CONNECT TO SQLSVR as con1
		(Datasrc=CIDWH authdomain=MSSQLCIDB1);
	CREATE TABLE &m_abt..&pre.SSN_SUM AS
		select *
			from connection to con1(
				
					select 
					SUM(RIRA_stg.SUM_qty) AS SSN_SUM
					FROM &m_dds..RAW_RETAIL_INTERNET_ABT_stg RIRA_stg
					WHERE RIRA_stg.TRANSDATE BETWEEN DATEADD(YY, -1, %Cnv_date_MSsql(&mv_abt_dt_.+&delay_p.+1)) 
						and DATEADD(YY, -1, %Cnv_date_MSsql(&mv_abt_dt_.+&delay_p.+&purchase_p.))
								AND RIRA_stg.CATEGORY = %quote(%'&category_n%')
									AND RIRA_stg.SUBCATEGORY = %quote(%'&subcategory_n%')

								
				
								);
	DISCONNECT FROM con1;
quit;

the whole code is

%macro create_target_v2(delay_p, purchase_p);

%macro d;
%mend d;

%if &mv_score = 0
%then %do;

proc sql;
CONNECT TO SQLSVR as con1
(Datasrc=CIDWH authdomain=MSSQLCIDB1);
CREATE TABLE &m_abt..&pre.SEASON as
select *
from connection to con1(
      select 
      case
          when month(RIRA_stg.TRANSDATE) in (12,1,2,) then 'Winter'
          when month(RIRA_stg.TRANSDATE) in (3,4,5) then 'Spring'
          when month(RIRA_stg.TRANSDATE) in (6,7,8) then 'Summer'
          when month(RIRA_stg.TRANSDATE) in (9,10,11) then 'Autumn'
      end 
      as SEASON
      FROM &m_dds..RAW_RETAIL_INTERNET_ABT_stg RIRA_stg      
      );
DISCONNECT FROM con1;
quit;
      




proc datasets lib=&m_abt.;
modify &pre.SEASON;
/*label
SEASON=""*/
;
run;

%end;

%mend create_target_v2;

%create_target_v2(&delay_period., &purchase_period.);

Re: Stucked with connection to SQLSVR

RW9 — Fri, 15 Mar 2019 10:50:36 GMT

Well, one thing which jumps out at me, syntax for case statement is:

case <statement> when <condition> then <result> else <result> end

or

case when <condition> then <result> else <result> end

I.e. there should always be an else part of a case statement.

Other than that, without knowing the macro variable information I can't see anything syntactically wrong.

Re: Stucked with connection to SQLSVR

Alex91 — Fri, 15 Mar 2019 11:20:36 GMT

Thanks, you are totally right about case statement but anyway it didn't help for me still getting the same error =(

Perhaps that's macro variable issue.

Re: Stucked with connection to SQLSVR

VDD — Fri, 15 Mar 2019 11:21:48 GMT

another error is you have an incorrect comma in this statement

when month(RIRA_stg.TRANSDATE) in (12,1,2,) then 'Winter'

remove the , after the last 2 also

Do you know SAS or are you changing someone else's code. You could always ask the original owner of the code for help.

Re: Stucked with connection to SQLSVR

RW9 — Fri, 15 Mar 2019 11:24:12 GMT

In which case turn on macro print options:

options mlogic mprint symbolgen source source2;

Before your SQL, and look at the log and see what code is being generated. Ensure that it is correct and valid code.

Re: Stucked with connection to SQLSVR

Alex91 — Fri, 15 Mar 2019 11:46:45 GMT

I'm totally confused about this comma! Seems like i'm blind.

Yes, that's my own code.

thanks a lot.

Re: Stucked with connection to SQLSVR

VDD — Fri, 15 Mar 2019 11:50:10 GMT

@Alex91 has when month(RIRA_stg.TRANSDATE) in (12,1,2,) then 'Winter'

change it to when month(RIRA_stg.TRANSDATE) in (12,1,2) then 'Winter'

do you see you had 2,) but it should be 2)