How to automatically change date in queries

Accepted Solution Solved
Reply
N/A
Posts: 1
Accepted Solution

How to automatically change date in queries

Hi everyone!

I do a report every week that involves about 20 queries. Each week I have to go to all 20 and modify and change the date to the new week ending.

Since they would all need the same week ending date, is there any way I could modify the code (or is there an option) to enter the new date once and have it automatically change for all the queries?

I use SAS EG 6.1

Any help would be greatly appreciated!

Thank you!


Accepted Solutions
Solution
‎02-09-2015 02:41 PM
Grand Advisor
Posts: 17,325

Re: How to automatically change date in queries

Create a macro variable or parameter in EG.

i.e. if it was via code:

%let end_date='01Jan2015'd;

Then in your queries replace the end date with &end_date wherever it's referenced.

proc sql;

create table report1 as

select *

from table1

where date between '01Jan2014'd and &end_date.;

quit;

View solution in original post


All Replies
Solution
‎02-09-2015 02:41 PM
Grand Advisor
Posts: 17,325

Re: How to automatically change date in queries

Create a macro variable or parameter in EG.

i.e. if it was via code:

%let end_date='01Jan2015'd;

Then in your queries replace the end date with &end_date wherever it's referenced.

proc sql;

create table report1 as

select *

from table1

where date between '01Jan2014'd and &end_date.;

quit;

Grand Advisor
Posts: 10,210

Re: How to automatically change date in queries

I think that an EG Prompt works with what Reeza supplied.

Frequent Contributor
Posts: 87

Re: How to automatically change date in queries

Could also automate the whole process so you just hit go each week. Use the code below to get the week end date (in this case Saturday) then use the macro throughout like others have suggested:

data _null_ ;

    weekend = intnx('week',date(),0,'end') ;

    call symput('wkend',weekend) ;

run ;

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 219 views
  • 4 likes
  • 4 in conversation