topic Re: DDE in SAS Procedures

DDE

Belle — Sun, 08 Mar 2015 01:14:15 GMT

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

Re: DDE

art297 — Sun, 08 Mar 2015 02:03:08 GMT

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

Re: DDE

Reeza — Sun, 08 Mar 2015 05:01:08 GMT

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.

Re: DDE

Belle — Sun, 08 Mar 2015 06:19:08 GMT

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;

Re: DDE

art297 — Sun, 08 Mar 2015 15:49:41 GMT

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