update query proc sql with where conditin returns error

[JJP1]

hi all,

i am trying to set the value for the column where date (date column contains timestamp also that is datetime19. format) is older than 7 years and iam using proc sql update option but getting following error. please help.

_
73
76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

proc sql;
vvv.jjj      set 
         jjj.id = 'FFFF'
      where jjj.date >=  %sysfunc(            
intnx(
            month,             
            %sysfunc(today()), 
            -6,                
            same               
            ), datetime19.  
           );
   ;
quit;

[Shmuel]

It is impossible to understand which dataset you want to update.

You probably miss some keywords and have an erroneous syntax.

Please post the full log in the '</> window in order to keep indentation.  

[JJP1]

proc sql;
   update vvv.jjj 
      set 
         jjj.id = 'FFFF'
      where jjj.date >=  %sysfunc(
            intnx(
            month,             
            %sysfunc(today()), 
            -6,                
            same               
            ), date9.  
           );
   ;
quit;

Updated @Shmuel  sorry for the error please

[gamotte]

Hello,

You can try as follows :

data have;
    format dt datetime19. id $4.;
    keep id dt;
    day=day(today());
    do i=0 to 10;
        id=put(i,best.);
        dt=dhms(intnx('day', today(),-i),0,0,0);
        output;
    end;
run;

proc sql noprint;
    UPDATE have
    SET id='FFFF'
    WHERE intck('day', datepart(dt), today())>6
    ;
quit;

[JJP1]

Yes thanks @gamotte , but i want to understand where iam going wrong in my syntax please.

iam unable to identify the error in my code can anyone please suggest

[gamotte]

What message is displayed in the log ?

You can use date functions in proc sql so you don't need sysfunc.

Also, you compare a date and a datetime.

[JJP1 · Accepted solution]

it is working if i try with below code please.

%let yera =
%sysfunc(intnx(year,%sysfunc(today()),-7,same),date9.);
proc sql;
   update vvv.jjj
      set 
         jjj.id = 'FFFF'
      where jjj.date >= "&yera"d
               ;
quit;

[gamotte]

It is generally preferable not to format date macrovariables, so you don't have to convert them back to date variables for comparisons.

%let yera =%sysfunc(intnx(year,%sysfunc(today()),-7,same));
proc sql;
   update vvv.jjj
      set 
         jjj.id = 'FFFF'
      where jjj.date >= &yera.
               ;
quit;

[gamotte]

See Maxim 28 :

https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers...