you're probably retreading old steps, but I've no code converter macro for this,. Hopefully others might have something to offer. However, I might be able to help a little on code generation for the timestamps Assuming these are loaded as names with a _dt suffix, here is a macro to generate the conversion of a generic one %macro dt( name )/des=' load from &name._dt ' ; /* expecting layout 2004-03-21-14.23.50.730219 */ &name= dhms( input( &name._dt, ?? yymmdd10. ) , input( scan( &name._dt, 4, '.-' ), ?? 2. ) , input( scan( &name._dt, 5, '.-' ), ?? 2. ) , input( scan( &name._dt, 6, '.-' ) !! '.' !! scan( &name._dt, 7, '.-' ) , ?? 11. ) ) ; attrib &name format=datetime. informat= datetime. ; %mend dt ; Using scan() allows 1-digit values for hour minute and second, which might cause problems for substr().

To apply that across all the timestamp data, I would use %mLoopsX( execut= dt, with = &timestamps ) where &timestamps contains a (space separated) list of the names of the timestamp variables.

%mloopsx features in the sas-l archives.... The most recent version uses %superq(). Let me know if that version doesn't turn up when you search.

Good Luck Peter Crawford Crawford Software Consultancy Limited

On Mon, 24 Jan 2005 18:28:39 -0500, Talbot Michael Katz <topkatz@MSN.COM> wrote:

>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"