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.