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
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!
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!
Worked perfectly! Thanks heaps! You have freed up my Monday's haha!
%let q=%sysfunc(intnx(day,%sysfunc(today()),-7),yymmddn8.);
%put &q ;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.