topic Re: Where between dates to pull last month in SAS Procedures

Where between dates to pull last month

BETO — Thu, 18 Apr 2013 18:30:00 GMT

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

Re: Where between dates to pull last month

DBailey — Thu, 18 Apr 2013 19:06:44 GMT

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

Re: Where between dates to pull last month

AncaTilea — Thu, 18 Apr 2013 19:10:59 GMT

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.

Re: Where between dates to pull last month

Ron_MacroMaven — Thu, 18 Apr 2013 20:54:29 GMT

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