Date:         Mon, 14 Jan 2002 09:39:15 -0500
Reply-To:     Larry Bertolini <bertolini.1@OSU.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Larry Bertolini <bertolini.1@OSU.EDU>
Organization: Ohio State University
Subject:      Re: Help w Table lookup w SAS formats
Content-Type: text/plain; charset=us-ascii

How about:


  /* add effective date range to your format */
PROC FORMAT;
  VALUE $GROUPN;
  'BBB19000101' - 'BBB20040101' = 'K'
  'BBB20040402' - 'BBB20041231' = 'A'
  ...
  ;


DATA;

  /* assuming that the dataset contains a variable */
  /* called "trandate", that is in 8-char yyyymmdd */
  /* format                                                         */
  LOOKUPKY = PAYOR || TRANDATE;
  GROUPN = PUT(LOOKUPKY,$GROUPN.);

"Carriere, Ron" wrote:

> Each week we process a SAS file that has from 250,000 to 600,000 records.
> The file has a variable that represents the primary payor for that record,
> and a date of service.
> We have a SAS program that maps these records to a set of 14 payor
> groupings, each of which gets assigned to a variable GROUPA--GROUPN.   The
> way that we currently do that is to use a set of 14 SAS formats,  $GROUPA--
> $GROUPN and PUT statements.
>
> GROUPA = PUT(PAYOR,$GROUPA.);
>
> and so on.
> The table that is being mapped by this process looks like this:
>
> PAYOR       GROUPA   GROUPB    GROUPC  ....  GROUPN
> AAA  GG GG   RR  C
> BBB  CC BB   XX  K
>
> and so on for upwards of 5,000 lines.
>
> Currently new mappings are added to the table by adding new PAYOR codes,
> but beginning this year we are going to an approach that will permit
> multiple mappings of the same payor based upon date.  So we might have, for
> instance.
>
> PAYOR       GROUPA   GROUPB    GROUPC  .... GROUPN   Effective Date
>
> AAA  GG GG   RR  C   19900101
> BBB  CC BB   XX  K   19900101
>
> BBB  GG BB   XX  A   20000402
>
> Payor BBB would have GROUPN = A for dates on or after 20000402 and K for
> dates from 19900101-20000401.
> These additions and changes will occur for different payors on any date, so
> that we can't conceptualize this as a set of yearly tables or monthly ,
> each in the format of the original table.
>
> We are thinking of abandoning our current approach and moving to a solution
> where we merge the data from SAS file to the table file with some
> convoluted brute force programming to handle the date ranges, perhaps using
> COBOL or another language, but we would be greatful for any suggestions
> which use SAS and involve fewer headaches.
>
> Thanks in advance,
>
> Ron Carriere
> UCLA Medical Center