Date:         Wed, 12 May 1999 10:52:44 -0500
Reply-To:     david.eppler@SARALEEDIRECT.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Dave SLDEppler <david.eppler@SARALEEDIRECT.COM>
Subject:      IF...THEN based upon formatted values
Content-Type: text/plain; charset=US-ASCII

     I have 2 files, the first contains prospects.  This is on a DB2 table
     containing @18 million records today.  I expect this table to max out
     around 50 million eventually.  Each prospect has a source number
     (there are @900 sources).
 
     In a separate file each source number has been assigned a "program".
     (Actually for testing, only some of the "A" sources/programs are in
     this file and everyone else is assumed to be a "B" program, but
     eventually this file will have "A", "B", "C" programs with everyone
     else assumed to be a "D" program).
 
     I can get the desired results by merging the prospect file and the
     program file by source, but with this many records, I don't want to
     pass the file any more than I have to.
 
     BTW... Later in my processing I need to perform certain actions based
     upon what PROGRAM a prospect is in, not what their source# is.
 
     Any advice either on fixing my current code, support for a separate
     merge step or ways to perform actions based upon the "formatted" value
     of a field would be greatly appreciated.
 
     Thanks,
 
     David Eppler
 
     Below is my code when trying to use the format in the select step.
 
     5          DATA SRCFMT;
     6              INFILE SOURCEIN;
     7              IF _N_ = 1 THEN FMTNAME = '$SRCECD';
     8              TYPE = 'C';
     9              RETAIN FMTNAME TYPE;
     10             INPUT @01 START   $8.     /*source #*/
     11                   @10 LABEL   $3.;    /*grade   */
     12
 
     NOTE: The infile SOURCEIN is:
           Dsname=SDDEV.UEPPLD.PVTLIB(OHPJMSIN),
           Unit=3390,Volume=TST032,Disp=SHR,Blksize=27930,
           Lrecl=133,Recfm=FB
 
     NOTE: 22 records were read from the infile SOURCEIN.
     NOTE: The data set WORK.SRCFMT has 22 observations and 4 variables.
     NOTE: The DATA statement used 0.16 CPU seconds and 2947K.
     13         PROC FORMAT CNTLIN=SRCFMT;
     NOTE: Format $SRCECD has been output.
     14
     NOTE: The PROCEDURE FORMAT used 0.05 CPU seconds and 3188K.
     15         PROC SQL FEEDBACK STIMER NOERRORSTOP;
     NOTE: The SQL Statement used 0.02 CPU seconds and 3367K.
     16             CONNECT TO DB2 (SSID=DB2P);
     NOTE: The SQL Statement used 0.05 CPU seconds and 3460K.
     17             CREATE TABLE PROSM.PROSM
     19                          AS
     20             SELECT * FROM CONNECTION TO DB2
     21             (     SELECT CUST_NBR AS PROSP_NBR
     22                         ,LIST_SEG_CD FORMAT=$SRCECD AS SOURCE
     23                         ,CASE GNDR_CD
     24                               WHEN 'M' THEN 'M'
     25                                        ELSE 'O'
     26                               END AS GENDER
        .
        .
        .
     36                   ORDER BY PROSP_NBR);
     NOTE: The SQL Statement used 0.06 CPU seconds and 3751K.
     ERROR: A DB2 error has occurred while processing this statement.
     -104  DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "=". SOME
     SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO
 
     *********************************************************************
     When I tried to define the column I got this...
     16             CONNECT TO DB2 (SSID=DB2P);
     NOTE: The SQL Statement used 0.05 CPU seconds and 3460K.
     17             CREATE TABLE PROSM.PROSM
     18                         (SOURCE CHAR(3))
     19                          AS
     20             SELECT * FROM CONNECTION TO DB2
     21             (     SELECT CUST_NBR AS PROSP_NBR
     22                         ,LIST_SEG_CD FORMAT=$SRCECD AS SOURCE
        .
        .
        .
     36                   ORDER BY PROSP_NBR);
 
     ERROR: SAS data set options for the table being created may not
     include column  definitions.