topic Re: Date macro variable not resolved in SAS Programming

Date macro variable not resolved

amol123 — Mon, 07 Jan 2019 12:56:53 GMT

Dear All,

Please refer below code:

Precode -
%let start_time=%sysfunc(datetime());
 
Postcode - 
 
 
%let end_time = %qsysfunc(datetime());

%let to_date=%sysfunc(today(),date9.);
%put &to_date.;
proc sql;
select max(datepart(start_time)) format =date9.
into :date from target.test_control where job_name='ch_1';
run;
%put &date.;
 


proc sql;
select max(datepart(start_time))
format = date9. into :up_time from target.test_control where job_name='ch_1';
run;
%put &up_time.;


%let today_dt=%sysfunc(datepart(%sysfunc(datetime())),date9.);
%put &today_dt.;
 
%macro execute;
%if &up_time.= &today_dt. 
%then %do;
proc sql;
update target.test_control
set start_time=&start_time.,
end_time=&end_time.,
last_update_time=&end_time.,
status ='Y'
where job_name='ch_1' and &date.=&to_date.;
run;
%end;
%else %do;
proc sql;
insert into target.test_control values('flow_1',"&etls_jobname.",&start_time.,&end_time.,&end_time.,'sasdemo','Y');
run;
%end;
%mend;
%execute;

I am trying to update only new inserted(today's) observation in test_control table where max date of test_control table equal to today.

Scenario :

Trying to update few columns in test_control table based on two date macro variables which is created in above postcode section.
variable names and condition = &date.=&to_date. , While executing this code it return below error message.

date macro variable contains maximum updated date in control table and to_date variable contains system date, so i just want to update the observation where these two dates has equal value(todays date value).

Please refer "where" condition in macro.

Error Log -

184 07JAN2019
_______
22
76
MPRINT(EXECUTE): update target.test_control set start_time=1862501204.91143, end_time=1862501204.93361,
last_update_time=1862501204.93361, status ='Y' where job_name='ch_1' and 07JAN2019=07JAN2019;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET,
GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.

Thanks in advance.

Re: Date macro variable not resolved

PaigeMiller — Mon, 07 Jan 2019 14:33:53 GMT

Your title says "Date macro variable not resolved". Where does that wording come from?

Show us the entire SASLOG related to this code you are running. Click on the {i} icon and paste it into the window that opens.

Re: Date macro variable not resolved

Kurt_Bremser — Mon, 07 Jan 2019 14:36:24 GMT

Omit the date9 formats, and use the raw date values in your macro variables. Otherwise you need to make complete SAS date literals like

"&to_date."d

Re: Date macro variable not resolved

amol123 — Wed, 09 Jan 2019 06:15:52 GMT

Hi kurtBremser,

Thank you for your inputs. Solution provided by you is working now. While creating date i have assigned date7. format for date which is working fine with your suggestion. Also i have made some changes in my current code(Changed the where condition) which gives me expected outputs. Please refer updated code.

----------precode----------
%let start_time=%sysfunc(datetime());

----------postcode--------------
%let end_time = %qsysfunc(datetime());


%let to_date=&sysdate.;
%put &to_date.;


proc sql;
select max(datepart(start_time)) format =date7.
 into :date1 from target.job_control where job_name="&etls_jobname.";
run;
%put &date1.;






proc sql;
select max(datepart(start_time)) format =date7.
 into :up_time1 from target.job_control where job_name="&etls_jobname.";
run;
%put &up_time1.;






/*%put dt=%sysfunc(datepart(%sysfunc(datetime())));*/
/*%let today_dt1=%sysfunc(datepart(%sysfunc(datetime())));*/
%let today_dt1=&sysdate;
%put &today_dt1.;

data aa;
todate=put(today(),best.);
call symput ('today_dt',todate);
run;

%put &today_dt.;


%macro execute;
%if  "&up_time1"d = "&today_dt1"d 
%then %do;
proc sql;
update  target.job_control
set start_time=&start_time.,
 end_time=&end_time.,
 status ='Y'
/*where  "&date1"d="&to_date"d and  job_name='ch_1' and status='N';*/
where  datepart(start_time)=%sysfunc(today()) and  job_name="&etls_jobname.";
run;
%end;      

%else %do;

proc sql;
insert into target.job_control values('Dashboard1',"&etls_jobname.",&start_time.,&end_time.,'sasdemo','Y');
run;
%end;
%mend;
%execute;



Once again thank you for your valuable time.