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;
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;
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.
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
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;
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
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.