Hi.

I have a pipe-delimited ("|") ASCII file taken from a DB2 table, and a "DDL" layout of that table. I was wondering whether there was a canned macro (pre-SAS v9) to read the ASCII file into SAS according to the DDL specifications. The DDL looks like this (of course, the names have been changed, so if one comes up twice by accident, don't worry):

CREATE TABLE "BBB"."XXX_TAB" ( "V1_ID" VARCHAR(15) NOT NULL , "V2_ID" VARCHAR(50) , "C3_ID" VARCHAR(30) , "P1_ORG" VARCHAR(100) , "O1_ORG" CHAR(2) , "A_NME" VARCHAR(100) , "A_ATUS" VARCHAR(30) , "PL_CD" VARCHAR(15) , "PL_NM" VARCHAR(30) , "LAST_UPD" TIMESTAMP NOT NULL , "MF_NUM" VARCHAR(40) , "MP_NUM" VARCHAR(40) , "AC_NME" VARCHAR(100) NOT NULL , "AC_TYE" VARCHAR(30) , "P_ACCT" VARCHAR(15) , "PI_CD" VARCHAR(15) , "PI_NM" VARCHAR(60) , "AC_GRA" VARCHAR(30) , "AC_HGI" CHAR(1) , "AC_HGI_DT" TIMESTAMP , "AC_HQ" VARCHAR(30) , "AC_PB" VARCHAR(30) , "AC_TI" VARCHAR(30) , "AC_TI_DT" TIMESTAMP , "AL_NME" VARCHAR(100) , "ARL_AMT" DECIMAL(22,7) , "ARLC_CD" VARCHAR(15) , "ARU_AMT" DECIMAL(22,7) , "CO_HGI" CHAR(1) , "CO_HGI_DT" TIMESTAMP , "CO_PB" VARCHAR(30) , "CS_HGI" CHAR(1) , "CS_HGI_DT" TIMESTAMP , "CS_PB" VARCHAR(30) , "CIS_CD" VARCHAR(30) , "CV_ID" VARCHAR(30) , "CV_RN" VARCHAR(30) , "CV_ST" VARCHAR(30) , "CV_TE" VARCHAR(30) , "CT_OP" VARCHAR(30) , "CT_S_CD" VARCHAR(30) , "CT_VE" VARCHAR(30) , "BG_BR" VARCHAR(30) , "BG_DC_HE" VARCHAR(30) , "AC_CS" VARCHAR(30) , "CRS_FLG" VARCHAR(30) , "CTS_UED" VARCHAR(30) , "A0_DC_HE" VARCHAR(30) , "E_C_DC_HE" VARCHAR(30) , "N_DC_HE" VARCHAR(30) , "R0_DC_HE" VARCHAR(30) , "S_DC_HE" VARCHAR(30) , "WN_DC_HE" VARCHAR(30) , "D_PY_FLG" CHAR(1) , "DQ_SS" VARCHAR(30) , "D_NU" VARCHAR(15) , "D_P_NU" VARCHAR(15) , "D_M_T_NU" VARCHAR(15) , "D_L_T_NU" VARCHAR(15) , "EM_CO" DECIMAL(22,7) , "D_DIS" VARCHAR(9) , "D_P_IND" VARCHAR(1) , "D_HIEHY" VARCHAR(2) , "D_L_D_NU" VARCHAR(16) , "D_L_SM" VARCHAR(30) , "D_V_S" VARCHAR(15) , "D_IC" VARCHAR(30) , "D_TUS" VARCHAR(30) , "ESE_EM" DECIMAL(22,7) , "ESE_RE" DECIMAL(22,7) , "ESE_V_R" DECIMAL(22,7) , "ESB_V" DECIMAL(22,7) , "ESB_V_R" DECIMAL(22,7) , "EXC_RN" VARCHAR(30) , "FAL_HTH" VARCHAR(30) , "HIG_U" VARCHAR(30) , "ICC_SCE" VARCHAR(30) , "IC" VARCHAR(15) , "IS_DON" VARCHAR(30) , "IS_BET" VARCHAR(30) , "IT_BET" VARCHAR(30) , "LA_VTE_DT" TIMESTAMP , "LEL_TUS" VARCHAR(30) , "LNS_NU" DECIMAL(10,0) , "MIY_OWD" VARCHAR(30) , "WEN_OWD" VARCHAR(30) , "MK_EA" VARCHAR(30) , "PAER" VARCHAR(100) , "RUE_R" DECIMAL(22,7) , "SUIE_FLG" CHAR(1) , "TIEUAD" VARCHAR(30) , "PR_DR" VARCHAR(200) , "PR_TY" VARCHAR(50) , "PR_TE" VARCHAR(10) , "PR_X_TE_DE" VARCHAR(255) , "PR_DE" VARCHAR(30) , "PR_NTY" VARCHAR(50) , "PR_TRY" VARCHAR(30) , "PR_DR_T_UPD" TIMESTAMP , "MN_DR" VARCHAR(200) , "MN_TY" VARCHAR(50) , "MN_TE" VARCHAR(10) , "MN_X_TE_DE" VARCHAR(255) , "MN_DE" VARCHAR(30) , "MN_NTY" VARCHAR(50) , "MN_TRY" VARCHAR(30) , "MN_DR_T_UPD" TIMESTAMP ) PARTITIONING KEY ("V1_ID") USING HASHING IN "DM0_L_DAT" INDEX IN "DM0_L1_IDX" ;

If I have to write the macro myself, I will, but it's busy work. For example, the timestamp data looks like this: 2004-03-21-14.23.50.730219 but SAS v8 doesn't recognize that as a datetime value, so it has to be transformed (which might be outside the capability of an existing macro; I can do it in a laborious fashion, but if someone has an efficient way to transform the datetimes, please let me know).

Thanks!

-- TMK -- "The Macro Klutz"