DDE

[Belle]

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

[art297]

Don't you want R37C3:R39C ?  What is the value of &cur_mm, ?

[Belle]

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;

[Reeza]

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.

[art297]

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";