Help using Base SAS procedures

Where between dates to pull last month

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

Where between dates to pull last month

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


Accepted Solutions
Solution
‎04-18-2013 03:06 PM
Super Contributor
Posts: 578

Re: Where between dates to pull last month

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

View solution in original post


All Replies
Solution
‎04-18-2013 03:06 PM
Super Contributor
Posts: 578

Re: Where between dates to pull last month

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

Super Contributor
Posts: 543

Re: Where between dates to pull last month

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.

Regular Contributor
Posts: 198

Re: Where between dates to pull last month

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 249 views
  • 1 like
  • 4 in conversation