Hi ... here's another idea, transpose the spreadsheet using the libname engine then use SQL to assign variable names and types ... libname x 'z:\rawdata.xls' getnames=no scantime=no; proc transpose data=x.'sheet1$'n out=new; var f4-f20; by f1 f2; run; libname x clear; proc sql; create table xyz as select f1 as id, f2 as name, col7 as date, input(col6,comma10.) as cash, input(col5,comma10.) as credit, input(col4,comma10.) as supplies, col3 as other, col2 as total, col1 as equipment from new; quit; If those "@NA" variables are really supposed to be missing numeric data ... proc format; invalue fix @NA = . other=[comma10.]; value fixed . = '@NA' other=[10.]; run; proc sql; create table xyz as select f1 as id, f2 as name, col7 as date, input(col6,comma10.) as cash, input(col5,comma10.) as credit, input(col4,comma10.) as supplies, input(col3,fix.) as other format=fixed., input(col2,fix.) as total format=fixed., input(col1,fix.) as equipment format=fixed. from new; quit;
... View more
