HI I have this snippet of code where I'm trying to pull last month data. I would like it to pull last month data no matter when it's run. thanks for your assistance ... I attach error log
data temp;
curr_date = "10Mar2013"d;
first_day_prev_month = intnx('month',curr_date,-1,'B');
last_day_prev_month = intnx('month1',curr_date,-1,'E');
call symput('dt', "'"||put(first_day_prev_month, mmddyyd10.)||"'");
call symput('dt1', "'"||put(last_day_prev_month, mmddyyd10.)||"'d");
format curr_date first_day_prev_month last_day_prev_month mmddyyd10.;
run;
proc sql;
connect to Access as db (path="T:\CCMS Production\CCMSProduction.accdb");
create table Dep as
select * from connection to db (select * from [tbl_depositcompliance]
where tbl_depositcompliance.Scheduled_Settle between &dt. and &dt1.
);
quit;
1901 proc sql;
1902 connect to Access as db (path="T:\CCMS Production\CCMSProduction.accdb");
1903 create table Dep as
1904 select * from connection to db (select * from [tbl_depositcompliance]
1905 where (tbl_depositcompliance.Scheduled_Settle
SYMBOLGEN: Macro variable DT resolves to '02-01-2013'
1905! between &dt.
SYMBOLGEN: Macro variable DT1 resolves to '02-28-2013'd
1905! and &dt1.)
1906 );
ERROR: Prepare: Syntax error (missing operator) in query expression
'(tbl_depositcompliance.Scheduled_Settle between '02-01-2013' and '02-28-2013'd)'.
SQL statement: select * from [tbl_depositcompliance] where (tbl_depositcompliance.Scheduled_Settle
between '02-01-2013' and '02-28-2013'd)
1907
1908 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 4.42 seconds
cpu time 0.03 seconds
I think the pass through query has to use native syntax which for access is #mm/dd/yyyy#
So, try
call symput('dt', "#"||put(first_day_prev_month, mmddyys10.)||"#");
call symput('dt1', "#"||put(last_day_prev_month, mmddyys10.)||"#");
I think the pass through query has to use native syntax which for access is #mm/dd/yyyy#
So, try
call symput('dt', "#"||put(first_day_prev_month, mmddyys10.)||"#");
call symput('dt1', "#"||put(last_day_prev_month, mmddyys10.)||"#");
Hi,
could you try this, please (I added a 'd' at the end of your "dt" variable (see in red)
data temp;
curr_date = "10Mar2013"d;
first_day_prev_month = intnx('month',curr_date,-1,'B');
last_day_prev_month = intnx('month1',curr_date,-1,'E');
call symput('dt', "'"||put(first_day_prev_month, mmddyyd10.)||"'d");
call symput('dt1', "'"||put(last_day_prev_month, mmddyyd10.)||"'d");
format curr_date first_day_prev_month last_day_prev_month mmddyyd10.;
run;
proc sql;
connect to Access as db (path="T:\CCMS Production\CCMSProduction.accdb");
create table Dep as
select * from connection to db (select * from [tbl_depositcompliance]
where tbl_depositcompliance.Scheduled_Settle between &dt. and &dt1.
);
quit;
Let me know if this works.
Good luck
Anca.
I see that proofreading has been posted
and is probably the answer.
fyi
the data step where you create the macro variables with call symput
can be replaced with macro assignment statements
details and examples are on this page:
http://www.sascommunity.org/wiki/Macro_Loops_with_Dates
the SGF.paper:
http://support.sas.com/resources/papers/proceedings13/343-2013.pdf
Ron Fehd dates maven
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.