BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
joshua_james
Calcite | Level 5

Hey guys.

 

I have a created a program that is going to grab data from one excel spreadsheet and chuck it into another one weekly. It is going to import the data into a specific named range for that week.

 

My question - Is there any code that I can use that changings the name in my code to the date 7 days ago?

 

For example, this is part of my current code -

 

libname xls excel 'H:\TEST.xlsm';

 

proc datasets lib = xls nolist ;

delete Q20160210 ;

quit ;

 

data xls.Q20160210 ;

set THISMONTH ;

run ;

 

libname xls clear;

 

Is there any way to get the progame to automatically change Q(Date) to Q(Todays Date - 7)?

 

Hopefully this makes sense and thanks in advance.

 

Josh

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Welcome to the world of macro variables.

Create a macro variable that is 7 days from today. Replace the portion of your code that is the date with the macro variable reference, in this case: &cdate_7.

 

I like to use a data _null_ step to create my macro variables as it's more intuitive. You could do it with just a single %let statement. There's a lot of code an examples online, once you know what to search for. 

 

data _null_;
cdate=today();
cdate_7 = today()-7;

call symputx('cdate_7', put(cdate_7, yymmddn8.));
run;

%put &cdate_7.;

And then modify your code accordingly.

 

proc datasets lib = xls nolist ;
delete Q&cdate_7. ;
quit ;
 
data xls.Q&cdate_7.;
set THISMONTH ;
run ;

Good Luck!

View solution in original post

3 REPLIES 3
Reeza
Super User

Welcome to the world of macro variables.

Create a macro variable that is 7 days from today. Replace the portion of your code that is the date with the macro variable reference, in this case: &cdate_7.

 

I like to use a data _null_ step to create my macro variables as it's more intuitive. You could do it with just a single %let statement. There's a lot of code an examples online, once you know what to search for. 

 

data _null_;
cdate=today();
cdate_7 = today()-7;

call symputx('cdate_7', put(cdate_7, yymmddn8.));
run;

%put &cdate_7.;

And then modify your code accordingly.

 

proc datasets lib = xls nolist ;
delete Q&cdate_7. ;
quit ;
 
data xls.Q&cdate_7.;
set THISMONTH ;
run ;

Good Luck!

joshua_james
Calcite | Level 5

 Worked perfectly! Thanks heaps! You have freed up my Monday's haha!

Ksharp
Super User
%let q=%sysfunc(intnx(day,%sysfunc(today()),-7),yymmddn8.);

%put &q ;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2342 views
  • 2 likes
  • 3 in conversation