Where between dates to pull last month

[BETO]

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

[DBailey · Accepted solution]

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.)||"#");

[AncaTilea]

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.

[Ron_MacroMaven]

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