Hi,
I have a quarterly report which needs to add new months for the reporting quarter. I'm wondering if there is a way I can automate adding these months, instead of changing the row number manually every time.
For example, last quarter, the reporting months range was R28C3:R36C9, but for new quarter, I need to add 3 rows to R31C3:R39C9
FILENAME DATA1 DDE "EXCEL|sheet1!R28C3:R36C9";
I tried to use macro, but it doesn't work.
FILENAME DATA2 DDE "EXCEL|sheet1!R&cur_mm.C11:R&cur_mm.C12";
Is there a easy way that I can add more rows for each reporting quarter?
Thanks
Don't you want R37C3:R39C ? What is the value of &cur_mm, ?
Hi Arthur,
I found the solution. It works now. Thanks.
Here is what I need:
Q3 2014: FILENAME DATA1 DDE "EXCEL|sheet1!R28C3:R36C9";
Q4 2014: FILENAME DATA1 DDE "EXCEL|sheet1!R28C3:R39C9";
Q1 2015: FILENAME DATA1 DDE "EXCEL|sheet1!R40C3:R42C9";
Q2 2015: FILENAME DATA1 DDE "EXCEL|sheet1!R40C3:R45C9";
Q3 2015: FILENAME DATA1 DDE "EXCEL|sheet1!R40C3:R48C9";
Q4 2015: FILENAME DATA1 DDE "EXCEL|sheet1!R40C3:R51C9";
I want the result like this:
Q4 2014: FILENAME DATA1 DDE "EXCEL|sheet1!R&BYR.C3:R&EQTR.C9";
Q1 2015: FILENAME DATA1 DDE "EXCEL|sheet1!R&BYR.C3:R&EQTR.C9";
What I did is manually enter the beginning year number, and calculate the end of the quarter.
for 2014 Q4,
%let BYRrow= 28;
data temp2;
TODAY = TODAY();
CURYR = PUT(YEAR(TODAY),Z4.);
CURQ = QTR(today);
format today date9.;
if CURQ = 1 then
mos_cur = MONTH(intnx('year',today,-1, 'end'));
else mos_cur = MONTH(intnx('Qtr',today,-2, 'end'));
BYR = &BYRrow. + mos_cur - 1;
CALL SYMPUTX('BYR',BYR);
RUN;
1. Why not just store the old info and overwrite the information?
2. I don't see what's wrong with your code. My first guess is that the macro variables have trailing spaces so it depends on how you're creating them.
You can test this by using MPRINT/SYMBOLGEN and see what's being executed.
If you're only trying to add 3 months of data at a time, I think your current formula will overwrite some of your data each time. Possibly something like the following might be easier to maintain:
%let year=2015;
%let qtr=1;
data _null_;
start=%eval(4+((&year.-2012)*12)+(&qtr.-1)*3);
end=start+2;
CALL SYMPUTX('BQTR',start);
CALL SYMPUTX('EQTR',end);
run;
FILENAME DATA1 DDE "EXCEL|sheet1!R&BQTR.C3:R&EQTR.C9";
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.