CODEWITHSUNDEEP
sqlsas
Przemek Dabek
Przemek Dabek

Reputation: 533

SQL how to resolve problem with syntax in SAS

Help me its shows me an error in where t1.data_od that I have a syntax error, but I don't know what is wrong here


    create table PolisyEnd as 

    select
    t1.obj_oid
    ,t1.PRP_AGREEMENT                
    ,t1.PRP_POLICYNUMBER
    ,t1.PRP_SOCIETY_PID
    ,t1.PRP_END_DATE                as PRP_END_DATE
    from 
        cmz.WMDTZDP_BH t1 ,
        where t1.data_od = &first_day. 
        and t1.data_do = &gv_date_dly.
left join 
        (select kontr_id,obj_oid from cmz.BH_D_ZAB_X_ALOK_&thismonth where data_danych = &gv_date_dly.) t2 on t2.obj_oid = t1.obj_oid ;

quit;

%let gv_date_dly=%sysevalf(%bquote('&date_dly.'d));
%let thismonth=%sysfunc(putn(%sysfunc(today()),yymmn10.));
%let first_day = %sysfunc(intnx(month,%sysfunc(today()),0,b));
%let last_day = %sysfunc(intnx(month,%sysfunc(today()),0,e));

Upvotes: 0

Views: 56

Answers (1)

Arun
Arun

Reputation: 1106

Assuming you have removed the comma next to cmz.WMDTZDP_BH t1, I guess there may be issue with LEFT JOIN that should be used before WHERE statement.

Let's make sure you execute macro variables first:

%let gv_date_dly=%sysevalf(%bquote('&date_dly.'d));
%let thismonth=%sysfunc(putn(%sysfunc(today()),yymmn10.));
%let first_day = %sysfunc(intnx(month,%sysfunc(today()),0,b));
%let last_day = %sysfunc(intnx(month,%sysfunc(today()),0,e));

Then, Your updated query may look like this (Placing LEFT JOIN before WHERE):

PROC SQL;         /* --> Just in case if you missed*/
create table PolisyEnd as 
    select
    t1.obj_oid
    ,t1.PRP_AGREEMENT                
    ,t1.PRP_POLICYNUMBER
    ,t1.PRP_SOCIETY_PID
    ,t1.PRP_END_DATE as PRP_END_DATE
from 
    cmz.WMDTZDP_BH t1
left join 
        (select kontr_id,obj_oid from cmz.BH_D_ZAB_X_ALOK_&thismonth where data_danych = &gv_date_dly.) t2 
on t2.obj_oid = t1.obj_oid
where t1.data_od = &first_day. 
   and t1.data_do = &gv_date_dly.;
quit;

Upvotes: 1

Related Questions