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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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