Help using Base SAS procedures

DDE

Reply
Contributor
Posts: 67

DDE

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

PROC Star
Posts: 7,471

Re: DDE

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

Contributor
Posts: 67

Re: DDE

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;


Super User
Posts: 19,780

Re: DDE

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.

PROC Star
Posts: 7,471

Re: DDE

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

Ask a Question
Discussion stats
  • 4 replies
  • 232 views
  • 6 likes
  • 3 in conversation