Date:         Mon, 13 Nov 2000 15:37:54 -0500
Reply-To:     "Fehd, Ronald J." <rjf2@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Fehd, Ronald J." <rjf2@CDC.GOV>
Subject:      Re: Macro Question
Comments: To: "Myra.Oltsik@RESPONSEINSURANCE.COM"
          <Myra.Oltsik@RESPONSEINSURANCE.COM>
Content-Type: text/plain; charset="iso-8859-1"

<sigh> where are the archives of Good Ideas when you really need them?

I believe that you want to have a data-driven set of macros

1. to be a hip user of macros you will need to think of subsetting your data
outside or before your number-crunching macro, which here appears to be
MOPEN.

2. you are on target t/MOPENRUN
   this is the way to generate the many calls to MOPEN.
   except -- tsk, tsk -- for the %GLOBAL Z statement: unnecessary

3. do a FREQ of your variables AcctQtr and TranDate
   I assume these will be parallel, meaning have same number of values
proc FREQ data = NCLM.QTRS;
          tables AcctQtr
          / out = ACCTQ(drop = Count Percent);
same for TranDate

take those data sets and turn them into macro arrays:
ACCT1 ACCT2 ACCT3
TRAN1 TRAN2 TRAN3 ...

then write a data step then splits out your data into the needed subsets
using a macro loop to generate the necessary subset names
DATA ACCT1 ACCT2 ACCT3 ...;
     set NCLM.QTRS;
and the necessary subsetting statements:
if AcctQtr eq "&ACCT1" then output ACCT1;
if AcctQtr eq "&ACCT2" then output ACCT2;

hope this skeletal recommendations helps

Ron Fehd  the macro maven  CDC Atlanta GA USA RJF2@cdc.gov
OpSys: WinNT   Ver: 8.1
---> cheerful provider of UNTESTED SAS code!*! <---


> -----Original Message-----
> From: Myra.Oltsik@RESPONSEINSURANCE.COM
> [mailto:Myra.Oltsik@RESPONSEINSURANCE.COM]
> Sent: Monday, November 13, 2000 2:49 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Macro Question
>
>
> I started building a macro to create a dataset for "Triangle" tables
> for the actuaries in my company. The diagonal is the number of claims
> open based on Accident Quarter and each successive diagonal (working
> in) would represent the outermost diagonal that you would currently
> generate had you run the report one quarter earlier. (I
> understand what
> they want, but ince I'm not an actuary, it's a bit hard for me to
> explain this further for you.)
>
> I ended up manually coding and running 12 versions of the code, but I
> want to learn how to do it using macros.
>
> I started with the following macros, but stopped when I realized one
> step was missing:
>
> =========================================
>    DATA Q;
>       INFILE DATALINES;
>       INPUT QTRD YYMMDD8. QTRN $6.;
>       DATALINES;
>    199712311997Q4
>    199803311998Q1
>    199806301998Q2
>    199809301998Q3
>    199812311998Q4
>    199903311999Q1
>    199906301999Q2
>    199909301999Q3
>    199912311999Q4
>    200003312000Q1
>    200006302000Q2
>    200009302000Q3
>    ;
>    RUN;
>
>    DATA _NULL_;
>       SET Q NOBS=NO;
>       CALL SYMPUT('OBSV',LEFT(PUT(NO,8.)));
>       STOP;
>    RUN;
>
>    %GLOBAL OBSV;
>
>    DATA _NULL_;
>       SET Q;
>       DO I = 1 TO &OBSV;
>          IF _N_ = I THEN DO;
>             CALL SYMPUT('QTRD'||LEFT(PUT(I,2.)),QTRD);
>             CALL SYMPUT('QTRN'||LEFT(PUT(I,2.)),QTRN);
>          END;
>       END;
>    RUN;
>
>    %MACRO MOPEN(QTR,QNAME);
>
>       DATA QTRS;
>          SET NCLM.QTRS;
>          BY COMPANY SUBLINE ACCQTR TRANSQTR;
>          IF ACCQTR EQ "&QNAME";
>       RUN;
>
>       DATA LOSSES;
>          SET NCLM.LOSSES;
>          WHERE UPCASE(INDMEXPN) = 'INDEMNITY' AND
>             TRANDATE LE &QTRD;
>       RUN;
>
>       . . . more code
>
>    %MEND MOPEN;
>
>    %MOPEN;
>
>    %MACRO MOPENRUN;
>
>       %GLOBAL Z;
>
>       %DO Z = 1 %TO &OBSV;
>          %MOPEN(&&QTRD&Z,&&QTRN&Z);
>       %END;
>
>    %MEND MOPENRUN;
>
>    %MOPENRUN;
>
> =========================================
>
> Here's the manual code I started with:
>
>    DATA QTRS;
>       SET NCLM.QTRS;
>       BY COMPANY SUBLINE ACCQTR TRANSQTR;
>       IF ACCQTR EQ '1997Q4' AND TRANSQTR EQ 1;
>    RUN;
>
>    DATA LOSSES;
>       SET NCLM.LOSSES;
>       WHERE UPCASE(INDMEXPN) = 'INDEMNITY' AND
>          TRANDATE LE '31DEC1997'D &STA ;
>    RUN;
>
> for the first time through, and ended with the following for the last
> time through:
>
>    DATA QTRS;
>       SET NCLM.QTRS;
>       BY COMPANY SUBLINE ACCQTR TRANSQTR;
>       IF (ACCQTR EQ '2000Q3' AND TRANSQTR EQ 1) OR
>          (ACCQTR EQ '2000Q2' AND TRANSQTR LE 2) OR
>          (ACCQTR EQ '2000Q1' AND TRANSQTR LE 3) OR
>          (ACCQTR EQ '1999Q4' AND TRANSQTR LE 4) OR
>          (ACCQTR EQ '1999Q3' AND TRANSQTR LE 5) OR
>          (ACCQTR EQ '1999Q2' AND TRANSQTR LE 6) OR
>          (ACCQTR EQ '1999Q1' AND TRANSQTR LE 7) OR
>          (ACCQTR EQ '1998Q4' AND TRANSQTR LE 8) OR
>          (ACCQTR EQ '1998Q3' AND TRANSQTR LE 9) OR
>          (ACCQTR EQ '1998Q2' AND TRANSQTR LE 10) OR
>          (ACCQTR EQ '1998Q1' AND TRANSQTR LE 11) OR
>          (ACCQTR EQ '1997Q4' AND TRANSQTR LE 12)
>       ;
>    RUN;
>
>    DATA LOSSES;
>       SET NCLM.LOSSES;
>       WHERE UPCASE(INDMEXPN) = 'INDEMNITY' AND
>          TRANDATE LE '30SEP2000'D &STA ;
>    RUN;
>
> How can I add the previous ACCQTR each time with the correct TRANSQTR
> using macro code?
>
> Thanks.
>
> Myra
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>